﻿/*

have to implement a parameter to add nullable typenames, specifically to support HierarchyId

	did simple udt support by drilling down to the base type but there may be another way for
	fnh using UserTypeConvention
 TODO: eliminate ClassMapWrap and use overrides? http://wiki.fluentnhibernate.org/Auto_mapping#Conventions
	but how to make it easy for the user to find part to override?
Pain points:
	Data Types:
	
		SqlHierarchyId is a struct and breaks the property setter guard and
		GetHashCode(). Some more plumbing needs to be emplaced. TODO:
		
		UDT are not supported.
		
		The concept of subtypes is not considered in this script.

	Fluent NHibernate: 	
	
		dropping FK fields is not an option with NH. trust me.
		
		FNH mapping does not support m:n with composite PK. You will need to 
		dump the hbm and tweak manually. You can then load a hybrid set of mappings
		if desired.
		
		See	http://stackoverflow.com/questions/2183417/fluent-nhibernate-how-to-map-mn-many-to-many-with-composite-keys-on-both-sides
		for info and if you have a solution.
		
		M:N persistence: the script does not attempt to (cannot?) discover the correct
		side to be flagged inverse and as of now. You will need to partial class the appropriate map and 
		handle the assignment in PostMap() override. UPDATE: apparently explicit mapping of junction table is
		enough. as a matter of fact, I am not setting inverse on any M:N and not setting any cascade option on
		any relation and am getting passing tests when saving m:n relations?
		
		1:1 is implemented as 2 references. perhaps 
		http://brunoreis.com/tech/fluent-nhibernate-hasone-how-implement-one-to-one-relationship/
		
		Tables with no PF and views are not mapped because FNH can't work with them and I am not about to
		create fake keys across all columns.
		
				
		Currently working on moving the relation rendering above the field rendering because, while basic nhibernate functionality was
		working great, I decided to run some tests with dropFkMembers off and boom. Those fields are dropped because they are
		already represented in the model by the object references. Now they are back and there are too many parameters in the
		sql statements. need to decorate them with .Not.Update().Not.Insert() - in order to do that I need to know which fields
		have been rendered on FKs. So - move relation rendering up above property rendering. wish me luck.
		conclusion: getting stuck on composite fk fields - for now you will have to make a design decision by extending the classmap
		
		
		
TODO:
	Add PK constructor to objects
	Add all field constructor to objects		
	
	


// <copyright project="DeadSimpleDTO2" file="DeadSimpleDTO2.sql" company="Sky Sanders">
// This source is subject to the Microsoft Public License (Ms-PL).
// Please see http://deadsimpledto.codeplex.com/license for details.
// All other rights reserved.
// </copyright>
// <version>2.04 beta</version>
--------------------------------------------------------------------------------

Project: DeadSimpleDTO2 : a SQL base C# POCO DTO code generator on steroids.
URL    : http://deadsimpledto.codeplex.com
Author : Sky Sanders <sky.sanders@gmail.com> http://skysanders.net/subtext
Date   : 01/31/2010

--------------------------------------------------------------------------------

Overview:

The core of DeadSimpleDTO2 is a single sql batch script that will generate C#
POCO DTO classes.

It is capable of identifying and properly rendering 1:M, M:1, M:N and 1:?
relations as Lists and object references.

It was written specifically to be runnable with no function or sproc
dependencies.

The script can be run stand-alone in a sql query tool, via ADO or most likely
using the included T4 template for code generation in Visual Studio 2008.

Running it either way with default options will produce flat, unadorned property
 bag C# DTO classes for each user table and view in a database.

Specifying one or more optional parameters can result in a finely tuned, robust
and feature rich library of DTO classes that can be used as is or extended via
partial classes for use in an ORM scenario or by the simple reflective loader
that is included.

The sample source code includes a test solution with a large variety of usage
examples with accompanying class diagrams.

Links to very short screen casts demostrating various use cases and quick starts
can be found at the end of this file as well.

Background:
--------------------------------------------------------------------------------
What started out as a simple sql script to generate some dto classes with fields
for, ironically enough, a sql server schema discovery tool, gradually grew to
what is now DeadSimpleDTO2.

Having been LLBLGen, EF and L2S bound for so long, once I started writing some
less than trivial SQL it felt good to be writing some procedural code again and
I just kept going. It seems to be a worthwile endeavor if only to give me
something interesting to brush up my sql chops on.

The best case scenario is that perhaps some may find it useful and someone who
IS a badass SQL developer takes a minor interest in the script and offers some
input.

Getting Started:
--------------------------------------------------------------------------------

QUICK START FOR THOSE IN THE KNOW AND THOSE THAT THINK THEY ARE.. ;-)

The basic process is as follows

1) Add a copy of DeadSimpleDTO2.ttinclude and DeadSimpleDTO2.sql to the root of
  a project. (see known issues and test project regarding subdirectories and
  using relative paths)

2) Add an .tt file from an item template if you have a T4 editor installed or
  simply add a text file  and specify .tt as the extension.

3) Add an include tag, open a script tag and set up the parameters for
  rendering. Basic example follows.

    <#@include file="DeadSimpleDTO2.ttinclude" #>
    <#

      var p = new DeadSimpleParameters();

      // get some paramters from app/web config if desired
      p.Load(this);

      // set some parameters in code, if desired

      p.renderSeparateFiles = true;
      p.rootNamespace="MyDTONamespace";


      // instantiate a renderer with this and the parameters
      var renderer = new DeadSimpleRenderer(this,p);


      // by default the include looks for the script in the same directory, if
      // we move it we have path it ourselves

      // renderer.ScriptPath =
      // System.IO.Path.GetDirectoryName(this.Host.TemplateFile)
      // + @"\DSDTO\DeadSimpleDTO2.sql";

      // render the code
      renderer.Render();

    #>

4) enjoy your generated classes.
                    |||||||
5) Optionally RTFM. VVVVVVV

TFM:
--------------------------------------------------------------------------------
All parameters, with the exception of connectionString and renderSeparateFiles,
are used by the script either in batch mode in a query tool or via a parameter
value object used by the included T4 template harness.

The deployed script has it's parameters and default values commented out to
allow calling via ado by the included T4 template.

To run the script in batch mode, simply uncomment the declarations and default
assignments, make desired changes to the assignments, set the tool's scope to
the target database or add a USE directive and run the script. The code will be
rendered via print statements.

The included T4 template include provides rich code generation facilities in the
context of any Visual Studio 2008 C# project/web site. It is likely the template
is also compatible with VS 2005 and 2010 but this has not been tested.

A basic understanding of behaviour of T4 templates may help you understand how
the template works but is not necessary to use the templates. The 'getting
started' project contains simple examples of usage, the linked screencasts
provide very quick walk-thoughs for common scenarios and edge cases as they
become apparent.

It is suggested to browse Oleg Sych's blog for T4 background and to download
Tangible's free T4 editor.

The test project contains a wide variety of use cases with the resultant class
diagrams for reference, edification and verification.

A suite of unit tests is being built to ensure the output but, as I have no
illusions that I am a SQL guru, I am hoping for a more targeted and experience
SQL programmer/DB architect to put some eyeballs on the generate class diagrams
and script to confirm or deny that my logic is correct and the resultant
code/diagrams accurately represent the datastore given the supplied parameters
before I start writing the 100's of tests required.

The number of parameters, listed below, may seem daunting but typically you will
need to set only a handful to produce a batch of robust DTO classes that you may
freely extend using partial classes. If one-off generation is desired/required,
simply copy the .cs file(s) to another project.

The harness, configuration and code generation functionality is contained in
DeadSimpleDTO2.ttinclude.

The rendering logic is completely contained in DeadSimpleDTO2.sql.

You may have as many .tt files that include DeadSimpleDTO2.ttinclude as desired.
See the test project for an example.

Parameters and connection string may be set entirely in code, entirely in
app/web config, entirely in an arbitrarily named configuration file, or a
combination of both. The rendering engine takes an instance of
DeadSimpleParameter. This class is set to the defaults listed below and is
configuration aware.

To load values from the project's app/web config simply call .Load(this).

To load values from an arbitrary configuration file, use the context of the
template to determine the path to the desired file and call .Load(this,path).

Configuration file based parameters are specified in AppSettings. Simply prepend
the parameter name with "DSDTO2-".

e.g.
<appSettings>
  <add key="DSDTO2-connectionString" value="Data Source= etc/>
  <add key="DSDTO2-identifierMode" value="Pascal"/>
  <add key="DSDTO2-renderINotify" value="true"/>
  ..... etc etc
</appSettings>

If a parameter is not found in the specified file, the current value set on the
parameter object will be used.

You may assign values after loading from a config file and before passing to the
rendering engine.

Again, see the test project and sample project for usage examples and resultant
class diagrams.

--------------------------------------------------------------------------------

Known Issues:

The templates are run in the context of the active solution project. If a
solution has but one project, the 'Transform All Text Templates' button on the
top of the solution explorer may work just fine for you, but if there are
multiple projects in the solution, you may want to run each template manually by
right-clicking the file and selecting 'Run Custom Tool'. YMMV.

The nature of the text transformation system is such that the templates are
compiled to temp assemblies. If you change a configuration file based parameter,
you may not get expected results upon running the template and/or may get an
obscure remoting exception. This is an issue with visual studio, not the
templates.

The quickest solution is to open the file that is calling the include and make
any modification, e.g. add and remove a space to set the dirty flag, and then
save the file. The template will be recompiled, the configuration file will be
reloaded and the tranformation will be processed.

Forward relative file paths are processed as expected by the t4 subsystem but it
seems not to like backing out of a directory. e.g. .\..\a child of my
parent\deadSimpleDTO2.ttinclude.

By default, the rendering engine expectes the sql script to be in the same
directory as the calling template. If it is placed elsewhere the scriptPath of
the renderer must explicitly be set. Again, as far as I can tell, only forward
relative paths are recognized.

I have toyed with using project items to located the script and include file to
enable adding a link to a common file instead of requiring a copy of both files
in each project that uses it but have met with mixed success and have put this
on hold.



IMPORTANT: I am desperately seeking persons with more experience and SQL
knowledge than I to review this script and it's output and provide feedback
and perhaps tips on how to optimize the code or advice to just drop it.

While managed languages are definitely better suited for this sort of task, I
think that a SQL based code generator is an interesting idea and am willing to
spend some time if it is well recieved.

Specific areas where help and advice are invited are the cardinality discovery
sections. There must be a better way to identify 1:? and M:N relationships than
 what I eventually came up with, after 3 days of pulling my hair out.....


PARAMTER REFERENCE: The Real FM

SQL/CLR/CONFIG Parameters
--------------------------+---------------------------+-------------------------
Name                      | Type                      | Default Value
--------------------------+---------------------------+-------------------------
--------------------------+---------------------------+-------------------------
rootNamespace             | NVARCHAR / string (256)   | NULL / string.Empty
--------------------------+---------------------------+-------------------------
           | The namespace in which to root all rendered DTO classes. If run in
           | batch mode, the default namespace is the Catalog name. If run from
           | the T4 template, the default namespace is the hosting project's
           | default namespace.
--------------------------+---------------------------+-------------------------
renderSchemaNamespace     | BIT / bool                | 0 / false
--------------------------+---------------------------+-------------------------
           | Further namespace objects with the name of the schema.
           | e.g. dbo.mynamespace.myDto
           | Can be necessary if multiple schemas contain similarobject names.
--------------------------+---------------------------+-------------------------
namespacesToInclude       | NVARCHAR / string (2048)  | NULL / string.Empty
--------------------------+---------------------------+-------------------------
           | A non-quoted, comma delimited string containing a list of
           | namespaces to introduce with using statements.
--------------------------+---------------------------+-------------------------
objectsToRender           | NVARCHAR / string (2048)  | NULL / string.Empty
--------------------------+---------------------------+-------------------------
           | A non-quoted, comma delimited string containing a list of tables
           | and views to be rendered.
           | Only listed objects will be rendered.
           | If objectsToRender is specified, objectsToExclude isignored.
           | The default value is NULL/string.Empty and will render all user
           | tables and views in all namespaces.
--------------------------+---------------------------+-------------------------
objectsToExclude          | NVARCHAR / string (2048)  | NULL / string.Empty
--------------------------+---------------------------+-------------------------
           | A non-quoted, comma delimited string containing a list of tables
           | and views to be excluded fromrendering.
           | Ignored if objectsToRender is specified.
--------------------------+---------------------------+-------------------------
membersToExclude          | NVARCHAR / string (2048)  | NULL / string.Empty
--------------------------+---------------------------+-------------------------
           | A non-quoted, comma delimited string containing a list of columns
           | and FK constraints to be excluded from rendering.
           | The format for columns is 'schema.table/view.column'
           | FK constraints may be dropped on one side or both.
           | To drop both sides of an FK, specify 'schema.constraint.'
           | To drop one side, specify 'schema.table+schema.constraint'
           |
           |
           | columns = schema.object.column
           |
           | e.g. dbo.Orders.CustomerId
           |
           | Foreign Keys = schema.constraint OR schema.object+schema.constraint
           | e.g.
           | dbo.FK_Order_DetailsOrders OR dbo.Orders+dbo.FK_Order_DetailsOrders
           |
           | the schema.object qualifier indicates which side of the
           | relationship to drop
           |
           | 	e.g.
           | @membersToExclude  =
           |		'dbo.Orders+dbo.FK_Orders_Customers,dbo.Orders.ShipCountry'
--------------------------+---------------------------+-------------------------
memberNameSubstitutions   | NVARCHAR / string (2048)  | NULL / string.Empty
--------------------------+---------------------------+-------------------------
           | A non-quoted, comma delimited string containing a  list of related
           | object names and substitutions.
           | Substitutions are performed only on FK relations.
           |
           | The format is 'schema.table.defaultName|substitution'
           |
           | default name will be the identifier that is rendered if no
           | substitutions are performed. E.G.  Northwind.Employees, with both
           | 1:M and M:1 relations set will render FK members Employees1 and
           | Employees2
           |
           | The replacement tokens are
           | dbo.Employees.Employees1|Supervisor,
           | dbo.Employees.Employees2|Subordinates
           |
           | See Northwind.Relations.MTO.tt and
           | Diagrams\Northwind\Northwind.Relations.MTO.cd
           | When specifying substitutions, you are responsiblefor preventing
           | collisions.
--------------------------+---------------------------+-------------------------
identifierMode            | NVARCHAR / string (10)    | NULL / string.Empty
--------------------------+---------------------------+-------------------------
           | Specifies how to handle embedded spaces and underscores in schema
           | names, object names and column names. The default behavior is
           | TO simply replace embedded invalid characters with underscores.
           |            
           | If collisions occur, E.g. dbo.Table_1 and dbo.Table 1,
           | the dbo.Table 1 will have an underscore added and in that there has
           | already been an object  named dbo.Table_1 rendered, it will be
           | named dbo.Table_11 and so on. Time to use substitutions.
--------------------------+---------------------------+-------------------------
renderRelationships       | BIT / bool                | false
--------------------------+---------------------------+-------------------------
           |  Render default 1:M relationships as empty Lists.
--------------------------+---------------------------+-------------------------
renderManyToOneRelations  | BIT / bool                | false
--------------------------+---------------------------+-------------------------
           | Render 1:M as empty Lists, M:1 and 1:? as object references
--------------------------+---------------------------+-------------------------
dropFkMembers             | BIT / bool                | false
--------------------------+---------------------------+-------------------------
           | If an FK column has been rendered as a List or object and is NOT a
           | component in ANY unique constraint, it will be dropped.
--------------------------+---------------------------+-------------------------
renderManyToManyRelations | BIT / bool                | false
--------------------------+---------------------------+-------------------------
           | Render M:N relations as empty Lists and omit the Junction table 1:M
           | member if it is composed entirely of key columns. If it contains
           | any columns that are not part of any constraint it is considered to
           | be data and will be rendered. If a junction table contains, for
           | instance a DateCreated field and you do not want it surfaced on
           | your object, you will need to either exclude it specifically or
           | conversely, not include it.
--------------------------+---------------------------+-------------------------
renderJunctionRelations   | BIT / bool                | false                           
--------------------------+---------------------------+-------------------------
           | Forces all junction 1:M collections to be rendered.
--------------------------+---------------------------+-------------------------
listMemberType            | NVARCHAR / string (256)   | 'IList'
--------------------------+---------------------------+-------------------------
           | The generic list type return value for a plural member. Do not
           | specify the angle brackets.
           | There is no constraint on the type you choose to specify, unless
           | you choose to use the provided rudimentary reflective data loader.
           | If you do choose to use DeadSimpleLoader, first of all, i feel
           | sorry for you ;-), but more importantly you will need to specify
           | 'renderLoaderAttributes' and ensure that the  listMemberType and
           | listInstanceTypeboth implement IList<>
--------------------------+---------------------------+-------------------------
listInstanceType          | NVARCHAR / string (256)   | 'List'
--------------------------+---------------------------+-------------------------
           | The generic type that will be instantiated to back the property.
--------------------------+---------------------------+-------------------------
renderDataContract        | BIT / bool                | 0 / false
--------------------------+---------------------------+-------------------------
           | Render a System.Runtime.Serialization.DataContract attribute on the
           | class and DataMember attributes on all members.
--------------------------+---------------------------+-------------------------
dataContractNamespace     | NVARCHAR / string (256)   | NULL / string.Empty
--------------------------+---------------------------+-------------------------
           |  The namespace for DataContract. in its entirety e.g.
           | http://mytempuri.org/mydeadsimpledtos
           |If null, only the Name argument will be supplied.
--------------------------+---------------------------+-------------------------
renderSerializable        | BIT / bool                | 0 / false
--------------------------+---------------------------+-------------------------
           |  Render System.Serializable on each class.
--------------------------+---------------------------+-------------------------
renderCommonBase          | BIT / bool                | 0 / false
--------------------------+---------------------------+-------------------------
           | Inherit all classes from a common partial base class The default
           | class implementation contains only a  byte array comparison routine
           |
           | If renderINotify is true, the INotifyPropertyChanged implementation
           | is housed in the baseclass regardless of the value of this paramter
--------------------------+---------------------------+-------------------------
commonBaseName            | NVARCHAR / string (256)   | 'DeadSimpleDTO'
--------------------------+---------------------------+-------------------------
           | The typename to use for the common base class, if rendered.
           | The format for specifying a generic base class is classname<$>.
           | Only one generic type parameter is supported and the format is
           | strict. '<$>', NO SPACES. This is a TSQL program for frak's sake.
           |
           | e.g. @commonBaseName='MyDTOBase<$>'
           |
           | On the DTO classes it will be rendered as
           |
           | public partial class MyTable : MyDTOBase<MyTable>
           |
           | and the base class will be rendered
           |
           | public partial class MyDTOBase<T> [: xxx] where T:new()
           |
           | If renderDataContract is specified, the DataContractName will be
           | 'MyTable' and the DataContract name of the base class will be
           | 'MyDTOBaseOf{0}' where 0 is the final generated typename of the DTO
           |
           | e.g.
           |
           | [DataContract([datacontract namespace,]Name="MyDTOOf{0}")]
           | public partial class MyDTO<T> :INotifyPropertyChanged where T:new()
           | {
           |
           | On the other end of the wire the type will be, for Orders,
           | MyDTOOfOrders.
           | It is probably a good idea to specify a datacontract namespace
           | especially if using a generic base class name.
           |
           | You can then use another partial part to constrain T and or inherit
           | from another base such as ActiveRecordBase<T>.
           |
           | psst.. that was just me making noise, I have not tested it yet.....
           |
           | see http://msdn.microsoft.com/en-us/library/ms731045.aspx for more
           | information on generic types and datacontract names.
--------------------------+---------------------------+-------------------------
renderINotify             | BIT / bool                | 0 / false
--------------------------+---------------------------+-------------------------
           | Implement INotifyPropertyChanged in a common base class.
--------------------------+---------------------------+-------------------------
renderLoaderAttributes    | BIT / bool                | 0 / false
--------------------------+---------------------------+-------------------------
           | Render attributes that enable the simple reflective dataloader,
           | DeadSimpleLoader. The attributes are rendered in the namespace of
           | the DTO thus carrying no dependencies. The loader discovers them
           | by name.
--------------------------+---------------------------+-------------------------
returnResultSet           | BIT / bool                | 0 / false
--------------------------+---------------------------+-------------------------
           | If false, simply render the code via PRINT statements. If running
           | as a batch the  output can be copied manually. If run via a
           | SqlConnection, handle the InfoMessage event, the code will be the
           | message.
           |
           | If true, returns a resultset formatted in such a way as to enable
           | the provided T4 template include file to render the classes in a
           | single file or in multiple files.
           |
           | Although you should not need to handle the resultset manually,
           | the format follows:

           | The resultset is comprised of two columns, [name] and [line].
           | The resultset is opened with [name]='NAMESPACE_OPEN',
           | [line] = render options listing behindcomment whacks, using
           | directives the root namespace and open curly brace.
           |
           | The dto types and support classes are then returned in chunks.

           | A type is signalled by a [name] and null [line]. The code for that
           | type is comprised of the following records with null [name] and
           | non-null [line].
           |
           | [name]='NAMESPACE_CLOSE' , [line]='}' signals the end of the result
--------------------------+---------------------------+-------------------------



CLR/CONFIG ONLY  Parameters

--------------------------+---------------------------+-------------------------
renderSeparateFiles       | bool                      | false
--------------------------+---------------------------+-------------------------
           | If true, each type is written to a seperate file names
           | [objectname].generated.cs nestedunder the calling template file.
           |
           | If false, the entire output is rendered under the calling template
           | as a single file.
           |
           | The template is source control aware and will check out a file
           | before writing to it if necessary.
           |
           | The template makes no attempt to manage the files. If the
           | parameters change you may wish to delete all nested file(s) before
           | running the transform.
--------------------------+---------------------------+-------------------------
connectionString          | string                    | string.Empty
--------------------------+---------------------------+-------------------------
           | The SqlServer 2005/2008 connection string for the target database.
--------------------------+---------------------------+-------------------------




REMOVE: objectPrefix AND objectSuffix
In use, they were playing havok with datacontract serialization and the necessary
code to fix it is not worth the effort. use substitutions instead

--------------------------+---------------------------+-------------------------
REMOVED: objectPrefix     | NVARCHAR / string (256)   | NULL / string.Empty
--------------------------+---------------------------+-------------------------
           | A string to prepend to the class name of each object rendered. The
           | value  is prepended AFTER any processing.
           | When specifying objectPrefix, you are responsible for preventing
           | collisions.
--------------------------+---------------------------+-------------------------
REMOVED: objectSuffix     | NVARCHAR / string (256)   | NULL / string.Empty
--------------------------+---------------------------+-------------------------
           | A string to append to the class name of each object rendered. The
           | value is append AFTER any processing.
           | When specifying objectSuffix, you are responsible for preventing
           | collisions.
--------------------------+---------------------------+-------------------------




-- NOTE:	Be sure to execute within the scope of the desired database.
--			You may wish to use a 'USE' statement if this script is called
--			interactively from a SQL tool e.g. USE Northwind

-- TODO: get column defaults to determine PK generator type for NHibernate and ActiveRecord
-- TODO: implement ActiveRecord attributes and Fluent NHibernate coe




*/


SET NOCOUNT ON
-- TODO:?? can implement simple check constraint in setters? probably a losing battle
-- TO-NOT-DO: r.e. fnh & totally dropped fk members
-- apparently i MUST render relationships FIRST in order
-- to get a record of rendered FK members to enable constructor generation
-- as well as equality method generation. 
--  e.g. when fk are dropped, OrderDetails should not look like this..
-- bool equals = (this.OrderID.Equals(temp.OrderID)) && (this.ProductID.Equals(temp.ProductID));
-- it should look like this
--  bool equals = (this.Orders.OrderID.Equals(temp.Orders.OrderID)) && (this.Products.ProductID.Equals(temp.Products.ProductID));
-- which opens the door to a lot more code in the equality section and elsewhere
-- why? to remove the unintuitive OrderId and ProductId fields which are effectively dummies.
-- AND to provide accurate equality comparisons... no way around it.

-- one solution is to just leave it up to the user to implement equals and gethashcode on those objects that require it,
-- i.e. composite primary keys. But my goal is to provide out-of-the box, compilable binding with at least nominal, if not better,
-- functionality against typical database schemas.

-- i wish i didn't care. my life would be easier.
-- (from unit tests) UPDATE: NEVERMIND - i have already written a gagillion notes above on why junction tables are treated differently and 
-- must have the keys set explicitly when creating them. me<-getting forgetful?
-- CONCLUSION: cannot fully drop FK fields on Junction tables. Perhaps on all others.
-- TODO: still need to fully examine the equality code in all cases.



-- NOTE: apparently Fluent NHibernate is not very discriminating when it refelects for maps.
--		Only one generated DTO set per assembly for now. Is problem with FNH , not DeadSimpleDTO.

-- NOTE: case sensitive comparisions are hardcoded as Latin1_General_CS_AS. Find out how to determine the current collation and how to get a CS version

-- TODO: if an enclosed type is named name as namespace compilation problems will ensue. provide default renaming behavior as well
-- as typeNameSubstitution

---- Input parameters - comment out when script is run via ADO

--BEGIN -- options
--	DECLARE @rootNamespace NVARCHAR(256),
--		@commonBaseName NVARCHAR(256),
--		@memberNameSubstitutions NVARCHAR(2048),
--		@namespacesToInclude NVARCHAR(2048),
--		@dataContractNamespace NVARCHAR(256),
--		@objectsToRender NVARCHAR(2048),
--		@objectsToExclude NVARCHAR(2048),
--		@membersToExclude NVARCHAR(2048),
--		@identifierMode NVARCHAR(10),
--		@listInstanceType NVARCHAR(256),
--		@listMemberType NVARCHAR(256),
--		@typeSubstitutions NVARCHAR(2048),
--		@referenceTypes NVARCHAR(2048),
--		@renderSchemaNamespace BIT,
--		@renderDataContract BIT,
--		@renderSerializable BIT,
--		@renderLoaderAttributes BIT,
--		@renderINotify BIT,
--		@renderCommonBase BIT,
--		@returnResultSet BIT,
--		@hideReadOnlySetters BIT ,
--		@renderOneToManyRelations BIT,
--		@renderManyToOneRelations BIT,
--		@renderManyToManyRelations BIT,
--		@eagerLoadEntities BIT,
--		@eagerLoadRelations BIT,
--		@renderFluentNHibernate BIT,
--		@renderDomainObjects BIT
					

--	------ Default options

--	SELECT @referenceTypes = NULL,
--		@rootNamespace = NULL,
--		@objectsToRender = NULL,
--		@objectsToExclude = NULL,
--		@membersToExclude = NULL,
--		@namespacesToInclude = NULL,
--		@listInstanceType = 'List',
--		@listMemberType = 'IList',
--		@identifierMode = NULL,
--		@dataContractNamespace = NULL,
--		@memberNameSubstitutions = NULL,
--		@commonBaseName = 'DeadSimpleDTO',
--		@renderSchemaNamespace = 0,
--		@renderDataContract = 1,
--		@renderSerializable = 0,
--		@renderLoaderAttributes = 1,
--		@renderINotify = 0,
--		@returnResultSet = 0,
--		@renderCommonBase = 0,
--		@renderFluentNHibernate = 1,
--		@typeSubstitutions = NULL,
--		@hideReadOnlySetters= 0,
--		@renderOneToManyRelations = 1,
--		@renderManyToOneRelations = 1,
--		@renderManyToManyRelations = 1,
--		@eagerLoadEntities= 0,
--		@eagerLoadRelations= 0,
--		@renderDomainObjects = 0
--END

BEGIN -- VARS 
	
	/*
		Refactoring in progress:
			bring all declared variables up here to the head,
			cull unused,
			redeclare for cases of unintuitive/lazy reuse 
			
	*/
		

	-- tmps - use them in an immediate scope
	-- eg use it and lose it within a couple lines of code
    DECLARE @tmp_sql VARCHAR(MAX),
        @count1 INT,
        @count2 INT
	
	-- constants
	DECLARE @nl CHAR(2)

        
	DECLARE @output_chunk NVARCHAR(MAX),
		@current_object_id INT,
        @current_object_schema NVARCHAR(128),
        @current_object_name NVARCHAR(128),
        @current_object_type NVARCHAR(2),
        @generated_class_namespace NVARCHAR(256),
        @generated_class_name NVARCHAR(256),
        @generated_property_name VARCHAR(256),
        @members_count INT,
        @substitution NVARCHAR(256),
        @commonBaseDataContractName NVARCHAR(256),
		-- full compliment of vars for cursoring over @tbl_all_table_columns
        @ccol_object_id INT,
        @ccol_table_schema NVARCHAR(128),
        @ccol_table_name NVARCHAR(128),
        @ccol_column_id INT,
        @ccol_column_name NVARCHAR(128),
        @ccol_column_type VARCHAR(128),
        @ccol_max_length INT,
        @ccol_is_nullable BIT,
        @ccol_is_primary_key BIT,
        @ccol_is_foreign_key BIT,
        @ccol_is_rowguidcol BIT,
        @ccol_is_identity BIT,
        @ccol_is_computed BIT,
        @ccol_default_definition NVARCHAR(MAX),
        @ccol_clr_type NVARCHAR(128),
        @ccol_is_value_type BIT,
        @ccol_member_name NVARCHAR(128), -- massaged, indexed and substituted
        @ccol_member_name_camel NVARCHAR(128)
        
		
    SELECT @output_chunk = '', @nl = CHAR(13) + CHAR(10)
	DECLARE @this_key NVARCHAR(MAX), @other_key NVARCHAR(MAX)

END

BEGIN -- argument validation
	-- ensure namespace
    IF ISNULL(@rootNamespace, '') = '' 
        BEGIN
            SELECT TOP 1
                    @rootNamespace = REPLACE([CATALOG_NAME], ' ', '_')
            FROM    [INFORMATION_SCHEMA].[SCHEMATA]
        END

    IF ISNULL(@dataContractNamespace, '') = '' 
        SET @dataContractNamespace = ''

    IF CHARINDEX('<', @commonBaseName) > 0 
        BEGIN
            SET @commonBaseDataContractName = REPLACE(@commonBaseName, '<$>', 'Of{0}')
        END
    ELSE 
        SET @commonBaseDataContractName = @commonBaseName

	-- cannot have INotify without base class
    IF @renderINotify = 1 
        SET @renderCommonBase = 1

END


BEGIN -- setup outer cursor, objects to be rendered
    DECLARE @tbl_excluded_objects TABLE ( [name] NVARCHAR(256) )
    IF ISNULL(@objectsToExclude, '') != '' 
        BEGIN
            WITH    Pieces ( pn, start, stop )
                      AS ( SELECT   1, 1, CHARINDEX(',', @objectsToExclude)
                           UNION ALL
                           SELECT   pn + 1, stop + 1, CHARINDEX(',', @objectsToExclude, stop + 1)
                           FROM     Pieces
                           WHERE    stop > 0 )
                INSERT  INTO @tbl_excluded_objects ( [name] )
                        SELECT  SUBSTRING(@objectsToExclude, start, CASE WHEN stop > 0 THEN stop - start
                                                                         ELSE LEN(@objectsToExclude)
                                                                    END) AS s
                        FROM    Pieces
        END


    DECLARE @tbl_objects_to_render TABLE ( [object_id] INT,[schema] NVARCHAR(128),[name] NVARCHAR(128),[type] NVARCHAR(32) )

    IF ISNULL(@objectsToRender, '') = '' 
        BEGIN -- All USER tables and views in current database
            INSERT  INTO @tbl_objects_to_render ( [object_id], [schema], [name], [type] )
                    SELECT  o.[object_id], s.[name] AS 'schema', o.[name], o.[type]
                    FROM    [sys].[objects] o
                    INNER JOIN [sys].[schemas] AS s
                    ON      o.[schema_id] = s.[schema_id]
                    WHERE   o.[type] IN ( 'U', 'V' )
                            AND ( s.[name] + '.' + o.[name] ) NOT IN ( SELECT   r.[name]
                                                                       FROM     @tbl_excluded_objects r )
                    --ORDER BY s.[name], o.[type]

        END
    ELSE 
        BEGIN -- All tables and views in @objectsToRender
            DECLARE @tmp_objects_to_render TABLE ( [name] NVARCHAR(256) ) ;
            WITH    Pieces ( pn, start, stop )
                      AS ( SELECT   1, 1, CHARINDEX(',', @objectsToRender)
                           UNION ALL
                           SELECT   pn + 1, stop + 1, CHARINDEX(',', @objectsToRender, stop + 1)
                           FROM     Pieces
                           WHERE    stop > 0 )
                INSERT  INTO @tmp_objects_to_render ( [name] )
                        SELECT  SUBSTRING(@objectsToRender, start, CASE WHEN stop > 0 THEN stop - start
                                                                        ELSE LEN(@objectsToRender)
                                                                   END) AS s
                        FROM    Pieces

            INSERT  INTO @tbl_objects_to_render ( [object_id], [schema], [name], [type] )
                    SELECT  o.[object_id], s.[name] AS 'schema', o.[name], o.[type]
                    FROM    [sys].[all_objects] o
                    INNER JOIN [sys].[schemas] AS s
                    ON      o.[schema_id] = s.[schema_id]
                    WHERE   o.[type] IN ( 'U', 'V' )
                            AND ( s.[name] + '.' + o.[name] ) IN ( SELECT   r.[name]
                                                                   FROM     @tmp_objects_to_render r )
                    --ORDER BY s.[name], o.[type]


        END

    DECLARE cCurrentObject CURSOR READ_ONLY
        FOR SELECT  [object_id], [schema], [name], [type]
            FROM    @tbl_objects_to_render
	            

END
BEGIN -- create temp tables

	-- store chunks of output here
	-- declaring with a id pk fixes odd rendering order
	-- problem exposed in adventureworks2008 salesorderheadermap
	DECLARE @tbl_output_type TABLE (
		[id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
		[name] [nvarchar](128) NULL,
		[line] [nvarchar](max) NULL) 	
	DECLARE @tbl_output_class_map TABLE (
		[id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
		[name] [nvarchar](128) NULL,
		[line] [nvarchar](max) NULL) 	
	DECLARE @tbl_tmp_class_map TABLE (
		[id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
		[name] [nvarchar](128) NULL,
		[line] [nvarchar](max) NULL) 	


	-- tmp table to store used identifiers to avoid collsion
	-- by appending a digit if necessary
	
    DECLARE @tbl_member_cache TABLE ( [name] NVARCHAR(256) )	
    DECLARE @tbl_types TABLE ( original NVARCHAR(1024),
                               new NVARCHAR(1024) )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'image', 'Byte[]' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'binary', 'Byte[]' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'varbinary', 'Byte[]' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'timestamp', 'Byte[]' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'text', 'String' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'ntext', 'String' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'char', 'String' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'nchar', 'String' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'varchar', 'String' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'nvarchar', 'String' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'xml', 'String' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'uniqueidentifier', 'Guid' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'date', 'DateTime' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'time', 'TimeSpan' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'datetime2', 'DateTime' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'datetimeoffset', 'DateTimeOffset' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'tinyint', 'Byte' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'smallint', 'Int16' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'int', 'Int32' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'smalldatetime', 'DateTime' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'real', 'Single' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'money', 'Decimal' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'datetime', 'DateTime' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'float', 'Double' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'sql_variant', 'Object' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'bit', 'Boolean' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'decimal', 'Decimal' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'numeric', 'Decimal' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'smallmoney', 'Decimal' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'bigint', 'Int64' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'hierarchyid', 'String' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'geometry', 'String' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'geography', 'String' )
    INSERT  INTO @tbl_types ( original, new )
    VALUES  ( 'sysname', 'String' )

    DECLARE @tbl_type_substitutions TABLE ( substitution NVARCHAR(1024) )
    IF ISNULL(@typeSubstitutions, '') != '' 
        BEGIN
            WITH    Pieces ( pn, start, stop )
                      AS ( SELECT   1, 1, CHARINDEX(',', @typeSubstitutions)
                           UNION ALL
                           SELECT   pn + 1, stop + 1, CHARINDEX(',', @typeSubstitutions, stop + 1)
                           FROM     Pieces
                           WHERE    stop > 0 )
                INSERT  INTO @tbl_type_substitutions ( substitution )
                        SELECT  SUBSTRING(@typeSubstitutions, start, CASE WHEN stop > 0 THEN stop - start
                                                                          ELSE LEN(@typeSubstitutions)
                                                                     END) AS s
                        FROM    Pieces

        END
    DECLARE cTypeSubs CURSOR READ_ONLY
        FOR SELECT  substitution
            FROM    @tbl_type_substitutions
    OPEN cTypeSubs
    FETCH NEXT FROM cTypeSubs INTO @tmp_sql
    WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE @original NVARCHAR(256)
            DECLARE @new NVARCHAR(256)
            SET @original = SUBSTRING(@tmp_sql, 1, CHARINDEX('|', @tmp_sql) - 1)
            SET @new = SUBSTRING(@tmp_sql, LEN(@original) + 2, LEN(@tmp_sql) - LEN(@original) - 1)
			
            IF EXISTS ( SELECT  *
                        FROM    @tbl_types
                        WHERE   original = @original ) 
                UPDATE  @tbl_types
                SET     new = @new
                WHERE   original = @original
            ELSE 
                INSERT  INTO @tbl_types ( original, new )
                VALUES  ( @original, @new )
				
            FETCH NEXT FROM cTypeSubs INTO @tmp_sql
        END
    CLOSE cTypeSubs
    DEALLOCATE cTypeSubs
	
	
    DECLARE @tbl_reference_types TABLE ( reference_type NVARCHAR(256) )
    IF ISNULL(@referenceTypes, '') != '' 
        BEGIN
            WITH    Pieces ( pn, start, stop )
                      AS ( SELECT   1, 1, CHARINDEX(',', @referenceTypes)
                           UNION ALL
                           SELECT   pn + 1, stop + 1, CHARINDEX(',', @referenceTypes, stop + 1)
                           FROM     Pieces
                           WHERE    stop > 0 )
                INSERT  INTO @tbl_reference_types ( reference_type )
                        SELECT  SUBSTRING(@referenceTypes, start, CASE WHEN stop > 0 THEN stop - start
                                                                       ELSE LEN(@referenceTypes)
                                                                  END) AS s
                        FROM    Pieces		
        END
	
	
    DECLARE @tbl_substitutions TABLE ( substitution NVARCHAR(256) )
    IF ISNULL(@memberNameSubstitutions, '') != '' 
        BEGIN
            WITH    Pieces ( pn, start, stop )
                      AS ( SELECT   1, 1, CHARINDEX(',', @memberNameSubstitutions)
                           UNION ALL
                           SELECT   pn + 1, stop + 1, CHARINDEX(',', @memberNameSubstitutions, stop + 1)
                           FROM     Pieces
                           WHERE    stop > 0 )
                INSERT  INTO @tbl_substitutions ( substitution )
                        SELECT  SUBSTRING(@memberNameSubstitutions, start, CASE WHEN stop > 0 THEN stop - start
                                                                                ELSE LEN(@memberNameSubstitutions)
                                                                           END) AS s
                        FROM    Pieces

        END


    DECLARE @tbl_members_to_exclude TABLE ( member_name NVARCHAR(128) )
    IF ISNULL(@membersToExclude, '') != '' 
        BEGIN

            WITH    Pieces ( pn, start, stop )
                      AS ( SELECT   1, 1, CHARINDEX(',', @membersToExclude)
                           UNION ALL
                           SELECT   pn + 1, stop + 1, CHARINDEX(',', @membersToExclude, stop + 1)
                           FROM     Pieces
                           WHERE    stop > 0 )
                INSERT  INTO @tbl_members_to_exclude ( member_name )
                        SELECT  SUBSTRING(@membersToExclude, start, CASE WHEN stop > 0 THEN stop - start
                                                                         ELSE LEN(@membersToExclude)
                                                                    END) AS s
                        FROM    Pieces

        END
    
    DECLARE @tbl_reserved_words TABLE ( word NVARCHAR(128) COLLATE Latin1_General_CS_AS )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'abstract' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'event' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'new' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'struct' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'as' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'explicit' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'null' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'switch' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'base' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'extern' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'object' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'this' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'bool' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'false' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'operator' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'throw' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'break' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'finally' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'out' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'true' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'byte' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'fixed' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'override' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'try' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'case' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'float' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'params' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'typeof' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'catch' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'for' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'private' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'uint' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'char' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'foreach' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'protected' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'ulong' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'checked' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'goto' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'public' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'unchecked' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'class' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'if' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'readonly' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'unsafe' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'const' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'implicit' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'ref' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'ushort' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'continue' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'in' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'return' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'using' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'decimal' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'int' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'sbyte' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'virtual' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'default' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'interface' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'sealed' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'volatile' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'delegate' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'internal' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'short' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'void' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'do' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'is' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'sizeof' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'while' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'double' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'lock' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'stackalloc' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'else' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'long' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'static' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'enum' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'namespace' )
    INSERT  INTO @tbl_reserved_words ( word )
    VALUES  ( 'string' )
    
        
    DECLARE @tbl_invalid_identifier_start TABLE ( chr NCHAR(1) )
    INSERT  INTO @tbl_invalid_identifier_start ( chr )
    VALUES  ( '0' )
    INSERT  INTO @tbl_invalid_identifier_start ( chr )
    VALUES  ( '1' )
    INSERT  INTO @tbl_invalid_identifier_start ( chr )
    VALUES  ( '2' )
    INSERT  INTO @tbl_invalid_identifier_start ( chr )
    VALUES  ( '3' )
    INSERT  INTO @tbl_invalid_identifier_start ( chr )
    VALUES  ( '4' )
    INSERT  INTO @tbl_invalid_identifier_start ( chr )
    VALUES  ( '5' )
    INSERT  INTO @tbl_invalid_identifier_start ( chr )
    VALUES  ( '6' )
    INSERT  INTO @tbl_invalid_identifier_start ( chr )
    VALUES  ( '7' )
    INSERT  INTO @tbl_invalid_identifier_start ( chr )
    VALUES  ( '8' )
    INSERT  INTO @tbl_invalid_identifier_start ( chr )
    VALUES  ( '9' )
    INSERT  INTO @tbl_invalid_identifier_start ( chr )
    VALUES  ( '0' )
    INSERT  INTO @tbl_invalid_identifier_start ( chr )
    VALUES  ( '.' )
	
	-- TODO: a table with invalid identifier characters
	-- very interesting article not (very) related http://www.lostechies.com/blogs/jimmy_bogard/archive/2009/02/16/spaces-in-identifier-names-in-c.aspx
	-- http://www.ecma-international.org/publications/files/ECMA-ST/Ecma-334.pdf section A.1.6, “Identifiers" or http://www.jaggersoft.com/csharp_grammar.html
END

BEGIN -- create column lists
    BEGIN -- Get a table full of PK and UQ columns
        DECLARE @tbl_unique_key_columns TABLE ( -- contains PK and UQ indexes
                                                table_schema NVARCHAR(128),
                                                table_name NVARCHAR(128),
                                                index_name NVARCHAR(128),
                                                column_id INT,
                                                column_name NVARCHAR(128),
                                                is_primary_key BIT,
                                                is_unique_constraint BIT,
                                                is_unique BIT,
                                                is_nullable BIT,
                                                is_rowguidcol BIT,
                                                is_identity BIT,
                                                default_definition NVARCHAR(MAX),
                                                user_type NVARCHAR(128),
                                                table_object_id INT )
        INSERT  INTO @tbl_unique_key_columns ( table_schema, table_name, index_name, column_id, column_name, is_primary_key, is_unique_constraint, is_unique, is_nullable, is_rowguidcol, is_identity, default_definition, user_type, table_object_id )
                               
        -- selects PK and UQ indexes
                SELECT  S.name AS schema_name, T.name AS table_name, IX.name AS index_name, IC.column_id, C.name AS column_name, IX.is_primary_key, IX.is_unique_constraint, IX.is_unique, C.is_nullable, C.is_rowguidcol, C.is_identity, d.definition, tp.NAME, T.[object_id]
                FROM    sys.tables AS T
                INNER JOIN @tbl_objects_to_render AS otr ON T.[object_id] = otr.[object_id] 
                INNER JOIN sys.schemas AS S
                ON      T.schema_id = S.schema_id
                INNER JOIN sys.indexes AS IX
                ON      T.object_id = IX.object_id
                INNER JOIN sys.index_columns AS IC
                ON      IX.object_id = IC.object_id
                        AND IX.index_id = IC.index_id
                INNER JOIN sys.columns AS C
                ON      IC.column_id = C.column_id
                        AND IC.object_id = C.OBJECT_ID
                INNER JOIN sys.types AS tp
                ON      C.user_type_id = tp.user_type_id
                LEFT OUTER JOIN sys.default_constraints AS d
                ON      T.object_id = d.parent_object_id
                        AND C.column_id = d.parent_column_id
                WHERE   ( IX.is_unique = 1 )
                        AND ( IX.is_unique = 1 )
                --ORDER BY schema_name, table_name, index_name, C.column_id
    END

    BEGIN -- Get a table full of FK columns

        DECLARE @tbl_foreign_key_columns TABLE ( constraint_name NVARCHAR(128),
                                                 base_schema_name NVARCHAR(128),
                                                 base_table_name NVARCHAR(128),
                                                 base_column_id INT,
                                                 base_column_name NVARCHAR(128),
                                                 unique_schema_name NVARCHAR(128),
                                                 unique_table_name NVARCHAR(128),
                                                 unique_column_id INT,
                                                 unique_column_name NVARCHAR(128),
                                                 base_object_id INT,
                                                 unique_object_id INT )
        INSERT  INTO @tbl_foreign_key_columns ( constraint_name, base_schema_name, base_table_name, base_column_id, base_column_name, unique_schema_name, unique_table_name, unique_column_id, unique_column_name, base_object_id, unique_object_id )
                SELECT  FK.name AS constraint_name, S.name AS base_schema_name, T.name AS base_table_name, C.column_id AS base_column_id, C.name AS base_column_name, US.name AS unique_schema_name, UT.name AS unique_table_name, UC.column_id AS unique_column_id, UC.name AS unique_column_name, T.[object_id], UT.[object_id]
                FROM    sys.tables AS T
                INNER JOIN @tbl_objects_to_render AS otr ON T.[object_id] = otr.[object_id] 
                INNER JOIN sys.schemas AS S
                ON      T.schema_id = S.schema_id
                INNER JOIN sys.foreign_keys AS FK
                ON      T.object_id = FK.parent_object_id
                INNER JOIN sys.foreign_key_columns AS FKC
                ON      FK.object_id = FKC.constraint_object_id
                INNER JOIN sys.columns AS C
                ON      FKC.parent_object_id = C.object_id
                        AND FKC.parent_column_id = C.column_id
                INNER JOIN sys.columns AS UC
                ON      FKC.referenced_object_id = UC.object_id
                        AND FKC.referenced_column_id = UC.column_id
                INNER JOIN sys.tables AS UT
                ON      FKC.referenced_object_id = UT.object_id
                INNER JOIN sys.schemas AS US
                ON      UT.schema_id = US.schema_id
                --ORDER BY base_schema_name, base_table_name
    END



    BEGIN -- Get a table full of columns
		-- TODO: ensure that the membername is not same as enclosing type
		
	

 

		
        DECLARE @tbl_all_table_columns TABLE ( [object_id] INT,
                                               table_schema NVARCHAR(128),
                                               table_name NVARCHAR(128),
                                               column_id INT,
                                               column_name NVARCHAR(128),
                                               column_type NVARCHAR(128),
                                               max_length INT,
                                               is_nullable BIT,
                                               is_primary_key BIT,
                                               is_foreign_key BIT,
                                               is_rowguidcol BIT,
                                               is_identity BIT,
                                               is_computed BIT,
                                               default_definition NVARCHAR(MAX),
                                               clr_type NVARCHAR(128),
                                               is_value_type BIT,
                                               member_name NVARCHAR(128),
                                               member_name_camel NVARCHAR(128) )
        
        INSERT  INTO @tbl_all_table_columns ( [object_id], table_schema, table_name, column_id, column_name, column_type, max_length, is_nullable, is_primary_key, is_foreign_key, is_rowguidcol, is_identity, is_computed, default_definition, clr_type, is_value_type )
                SELECT  tables.[object_id], schemas.name AS table_schema, tables.name AS table_name, columns.column_id, columns.name AS column_name, CASE datatypes.is_user_defined
                                                                                                                                                       WHEN 1 THEN ( SELECT TOP (1) NAME FROM sys . types WHERE (datatypes . system_type_id = system_type_id) )
                                                                                                                                                       ELSE datatypes.NAME
                                                                                                                                                     END AS column_type, columns.max_length, columns.is_nullable, 0 AS is_primary_key, CAST(0 AS BIT) AS is_foreign_key, columns.is_rowguidcol, columns.is_identity, columns.is_computed, defaults.[definition] AS default_definition, NULL, CAST(0 AS BIT)
                FROM    sys.all_columns AS columns
                INNER JOIN @tbl_objects_to_render AS otr ON columns.[object_id] = otr.[object_id] 
                INNER JOIN [sys].[all_objects] AS tables
                ON      columns.object_id = tables.object_id
                INNER JOIN sys.schemas AS schemas
                ON      tables.schema_id = schemas.schema_id
                LEFT OUTER JOIN sys.default_constraints AS defaults
                ON      columns.default_object_id = defaults.object_id
                LEFT OUTER JOIN sys.types AS datatypes
                ON      columns.user_type_id = datatypes.user_type_id

                
                
-- filter out excluded members
                WHERE   NOT EXISTS ( SELECT *
                                     FROM   @tbl_members_to_exclude
                                     WHERE  member_name = schemas.name + '.' + tables.name + '.' + columns.name )
                        AND NOT ( 0 = 1 AND EXISTS ( SELECT   * FROM     @tbl_foreign_key_columns WHERE    base_schema_name = schemas.NAME AND base_table_name = tables.name AND base_column_name = columns.name )
                                  AND NOT EXISTS ( SELECT   *
                                                   FROM     @tbl_unique_key_columns
                                                   WHERE    table_schema = schemas.name
                                                            AND table_name = tables.name
                                                            AND column_name = columns.name ) ) AND (tables.TYPE = 'U' OR tables.TYPE='V')
                --ORDER BY table_schema, table_name, columns.column_id


        -- kludge for sysname TODO: test this on system views - the sys views double up name identifiers
        UPDATE  @tbl_all_table_columns
        SET     column_type = 'nvarchar'
        WHERE   column_type = 'sysname'
          
		-- get the clr types 
        DECLARE cTypingLoop CURSOR
            FOR SELECT  [object_id], table_schema, table_name, column_id, column_name, column_type, max_length, is_nullable, is_primary_key, is_foreign_key, is_rowguidcol, is_identity, is_computed, default_definition, clr_type, is_value_type, member_name, member_name_camel
                FROM    @tbl_all_table_columns
            FOR UPDATE
				
        OPEN cTypingLoop
        FETCH NEXT FROM cTypingLoop INTO @ccol_object_id, @ccol_table_schema, @ccol_table_name, @ccol_column_id, @ccol_column_name, @ccol_column_type, @ccol_max_length, @ccol_is_nullable, @ccol_is_primary_key, @ccol_is_foreign_key, @ccol_is_rowguidcol, @ccol_is_identity, @ccol_is_computed, @ccol_default_definition, @ccol_clr_type, @ccol_is_value_type, @ccol_member_name, @ccol_member_name_camel 
        WHILE @@FETCH_STATUS = 0
            BEGIN
                UPDATE  @tbl_all_table_columns
                SET     clr_type = ISNULL((SELECT TOP(1) new FROM @tbl_types WHERE [@tbl_types].original=column_type),'Byte[]')
                WHERE CURRENT OF cTypingLoop

                SET @ccol_member_name = @ccol_column_name
                SET @ccol_member_name = REPLACE(@ccol_member_name, ' ', '_')
                SET @ccol_member_name = REPLACE(@ccol_member_name, '.', '_')

                IF @identifierMode = 'Pascal' 
                    BEGIN 
                        IF CHARINDEX(' ', @ccol_member_name, 0) > 0
                            OR CHARINDEX('_', @ccol_member_name, 0) > 0
                            OR ( SELECT @ccol_member_name COLLATE Latin1_General_CS_AS ) = UPPER(@ccol_member_name)
                            OR ( SELECT @ccol_member_name COLLATE Latin1_General_CS_AS ) = LOWER(@ccol_member_name) 
                            BEGIN

                                DECLARE @tmp_tbl_pascal_case_parts_all_table_columns TABLE ( [name] NVARCHAR(256) )
                                DELETE  @tmp_tbl_pascal_case_parts_all_table_columns ;
		                
                                WITH    Pieces ( pn, start, stop )
                                          AS ( SELECT   1, 1, CHARINDEX('_', @ccol_member_name)
                                               UNION ALL
                                               SELECT   pn + 1, stop + 1, CHARINDEX('_', @ccol_member_name, stop + 1)
                                               FROM     Pieces
                                               WHERE    stop > 0 )
                                    INSERT  INTO @tmp_tbl_pascal_case_parts_all_table_columns
                                            SELECT  SUBSTRING(@ccol_member_name, start, CASE WHEN stop > 0 THEN stop - start
                                                                                             ELSE LEN(@ccol_member_name)
                                                                                        END) AS s
                                            FROM    Pieces

                                SET @ccol_member_name = NULL
                                SELECT  @ccol_member_name = COALESCE(@ccol_member_name + '', '') + ( UPPER(SUBSTRING([name], 1, 1)) + LOWER(SUBSTRING([name], 2, LEN([name]) - 1)) )
                                FROM    @tmp_tbl_pascal_case_parts_all_table_columns
                            END
                        ELSE 
                            SET @ccol_member_name = UPPER(SUBSTRING(@ccol_member_name, 1, 1)) + SUBSTRING(@ccol_member_name, 2, LEN(@ccol_member_name) - 1)
                    END

			-- guard against numeric identifiers
                IF SUBSTRING(@ccol_member_name, 1, 1) IN ( SELECT   [chr]
                                                           FROM     @tbl_invalid_identifier_start ) 
                    SET @ccol_member_name = '_' + @ccol_member_name
				

			-- ensure the member type is not same as its parent object
                IF ( SELECT @ccol_member_name COLLATE Latin1_General_CS_AS ) = ( SELECT @ccol_table_name COLLATE Latin1_General_CS_AS ) 
                    SET @ccol_member_name = @ccol_member_name + '1'
				

                IF @ccol_member_name IN ( SELECT    word
                                          FROM      @tbl_reserved_words ) 
                    SET @ccol_member_name = '_' + @ccol_member_name				
								    
			---- guard against sys views sysname,nvarchar doubling of name edge case
                SELECT  @count1 = COUNT(*)
                FROM    @tbl_all_table_columns
                WHERE   member_name = @ccol_member_name
                        AND table_name = @ccol_table_name

                IF @count1 > 0 
                    BEGIN
						SET @ccol_member_name = @ccol_member_name + CAST(@count1 AS NVARCHAR(2))
                    END

                SET @ccol_member_name_camel = LOWER(SUBSTRING(@ccol_member_name, 1, 1)) + SUBSTRING(@ccol_member_name, 2, LEN(@ccol_member_name) - 1)

                IF @ccol_member_name IN ( SELECT    word
                                          FROM      @tbl_reserved_words ) 
                    SET @ccol_member_name = '_' + @ccol_member_name
			
                UPDATE  @tbl_all_table_columns
                SET     member_name = @ccol_member_name,
                        member_name_camel = @ccol_member_name_camel
                WHERE CURRENT OF cTypingLoop
			
			
			
							   
                FETCH NEXT FROM cTypingLoop INTO @ccol_object_id, @ccol_table_schema, @ccol_table_name, @ccol_column_id, @ccol_column_name, @ccol_column_type, @ccol_max_length, @ccol_is_nullable, @ccol_is_primary_key, @ccol_is_foreign_key, @ccol_is_rowguidcol, @ccol_is_identity, @ccol_is_computed, @ccol_default_definition, @ccol_clr_type, @ccol_is_value_type, @ccol_member_name, @ccol_member_name_camel 
            END
        CLOSE cTypingLoop
        DEALLOCATE cTypingLoop

		-- identify value types for equality and nullability analysis
		-- move this outside the cursor if not needed
        UPDATE  @tbl_all_table_columns
        SET     is_value_type = 1
        WHERE   clr_type NOT IN (  'String', 'Guid', 'Byte[]')		
        AND clr_type NOT IN (SELECT reference_type FROM @tbl_reference_types)
        UPDATE  @tbl_all_table_columns
        SET     clr_type = 'Nullable<' + clr_type + '>'
        WHERE   is_nullable = 1
                AND is_value_type = 1
		
    END


	-- update the table full of columns with info from the PK and FK table
	-- tried making it set based in one query but the query for finding
	-- pk and uq fields is more than just a join and was unmanagable
	
    UPDATE  @tbl_all_table_columns
    SET     is_primary_key = 1
    WHERE   EXISTS ( SELECT ukc.is_primary_key
                     FROM   @tbl_unique_key_columns ukc
                     WHERE  ukc.is_primary_key = 1
                            AND ukc.table_object_id = [@tbl_all_table_columns].[object_id]
                            AND ukc.column_id = [@tbl_all_table_columns].column_id )

    UPDATE  @tbl_all_table_columns
    SET     is_foreign_key = 1
    WHERE   EXISTS ( SELECT *
                     FROM   @tbl_foreign_key_columns fkc
                     WHERE  fkc.base_object_id = [@tbl_all_table_columns].[object_id]
                            AND fkc.base_column_id = [@tbl_all_table_columns].column_id )	  


	
END



BEGIN -- fix up all identifiers once before we go any farther

    DECLARE @fn_pascal_case_input NVARCHAR(256),
        @fn_pascal_case_output NVARCHAR(256),
        @fn_pascal_case_part NVARCHAR(128),
        @fn_pascal_case_sql VARCHAR(MAX)

    
	

	
    OPEN cCurrentObject
    FETCH NEXT FROM cCurrentObject INTO @current_object_id, @current_object_schema, @current_object_name, @current_object_type
    WHILE @@FETCH_STATUS = 0
        BEGIN
			-- perform mandatory identifier processing
            SET @generated_class_namespace = @current_object_schema

			-- todo: change this to a table scan	
            SET @generated_class_namespace = REPLACE(@generated_class_namespace, ' ', '_')
            SET @generated_class_namespace = REPLACE(@generated_class_namespace, '.', '_')				
			
 

            IF @identifierMode = 'pascal' 
                BEGIN
					-- massage the schema. unlikely it is necessary but you never know.
                    SET @fn_pascal_case_input = @generated_class_namespace
					-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                    BEGIN --> PascalCase Function common code
                        IF CHARINDEX(' ', @fn_pascal_case_input, 0) > 0
                            OR CHARINDEX('_', @fn_pascal_case_input, 0) > 0
					-- TSQL TIP: CS string comparisons on CI server
                            OR ( SELECT @fn_pascal_case_input COLLATE Latin1_General_CS_AS ) = UPPER(@fn_pascal_case_input)
                            OR ( SELECT @fn_pascal_case_input COLLATE Latin1_General_CS_AS ) = LOWER(@fn_pascal_case_input) 
                            BEGIN
                                SET @fn_pascal_case_output = ''

                                
                                DECLARE @tbl_pascal_case_parts_namespace TABLE ( [name] NVARCHAR(256) )
                                DELETE  @tbl_pascal_case_parts_namespace ;
                                
                                WITH    Pieces ( pn, start, stop )
                                          AS ( SELECT   1, 1, CHARINDEX('_', @fn_pascal_case_input)
                                               UNION ALL
                                               SELECT   pn + 1, stop + 1, CHARINDEX('_', @fn_pascal_case_input, stop + 1)
                                               FROM     Pieces
                                               WHERE    stop > 0 )
                                    INSERT  INTO @tbl_pascal_case_parts_namespace
                                            SELECT  SUBSTRING(@fn_pascal_case_input, start, CASE WHEN stop > 0 THEN stop - start
                                                                                                 ELSE LEN(@fn_pascal_case_input)
                                                                                            END) AS s
                                            FROM    Pieces

                                SET @fn_pascal_case_output = NULL
                                SELECT  @fn_pascal_case_output = COALESCE(@fn_pascal_case_output + '', '') + ( UPPER(SUBSTRING([name], 1, 1)) + LOWER(SUBSTRING([name], 2, LEN([name]) - 1)) )
                                FROM    @tbl_pascal_case_parts_namespace

                            END
                        ELSE 
                            SET @fn_pascal_case_output = UPPER(SUBSTRING(@fn_pascal_case_input, 1, 1)) + SUBSTRING(@fn_pascal_case_input, 2, LEN(@fn_pascal_case_input) - 1)
                    END
					-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                    SET @generated_class_namespace = @fn_pascal_case_output

                END             
            IF SUBSTRING(@generated_class_namespace, 1, 1) IN ( SELECT  [chr]
                                                                FROM    @tbl_invalid_identifier_start ) 
                SET @generated_class_namespace = '_' + @generated_class_namespace
                
            IF @generated_class_namespace IN ( SELECT   word
                                               FROM     @tbl_reserved_words ) 
                SET @generated_class_namespace = '_' + @generated_class_namespace
                
                				

			-- perform mandatory identifier processing				

            SET @generated_class_name = @current_object_name   


			-- todo: change this to a table scan
            SET @generated_class_name = REPLACE(@generated_class_name, ' ', '_') 
            SET @generated_class_name = REPLACE(@generated_class_name, '.', '_') 				
 		
            IF @identifierMode = 'pascal' 
                BEGIN					
					-- massage the object name
                    SET @fn_pascal_case_input = @generated_class_name
					-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
                    BEGIN --> PascalCase Function common code
                        IF CHARINDEX(' ', @fn_pascal_case_input, 0) > 0
                            OR CHARINDEX('_', @fn_pascal_case_input, 0) > 0
					-- TSQL TIP: CS string comparisons on CI server
                            OR ( SELECT @fn_pascal_case_input COLLATE Latin1_General_CS_AS ) = UPPER(@fn_pascal_case_input)
                            OR ( SELECT @fn_pascal_case_input COLLATE Latin1_General_CS_AS ) = LOWER(@fn_pascal_case_input) 
                            BEGIN
                                SET @fn_pascal_case_output = ''


                                
                                DECLARE @tbl_pascal_case_parts_object TABLE ( [name] NVARCHAR(256) )
                                DELETE  @tbl_pascal_case_parts_object ;
                                
                                WITH    Pieces ( pn, start, stop )
                                          AS ( SELECT   1, 1, CHARINDEX('_', @fn_pascal_case_input)
                                               UNION ALL
                                               SELECT   pn + 1, stop + 1, CHARINDEX('_', @fn_pascal_case_input, stop + 1)
                                               FROM     Pieces
                                               WHERE    stop > 0 )
                                    INSERT  INTO @tbl_pascal_case_parts_object
                                            SELECT  SUBSTRING(@fn_pascal_case_input, start, CASE WHEN stop > 0 THEN stop - start
                                                                                                 ELSE LEN(@fn_pascal_case_input)
                                                                                            END) AS s
                                            FROM    Pieces

                                SET @fn_pascal_case_output = NULL
                                SELECT  @fn_pascal_case_output = COALESCE(@fn_pascal_case_output + '', '') + ( UPPER(SUBSTRING([name], 1, 1)) + LOWER(SUBSTRING([name], 2, LEN([name]) - 1)) )
                                FROM    @tbl_pascal_case_parts_object


                            END
                        ELSE 
                            SET @fn_pascal_case_output = UPPER(SUBSTRING(@fn_pascal_case_input, 1, 1)) + SUBSTRING(@fn_pascal_case_input, 2, LEN(@fn_pascal_case_input) - 1)
                    END
					-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                    SET @generated_class_name = @fn_pascal_case_output
                END

            IF SUBSTRING(@generated_class_name, 1, 1) IN ( SELECT   [chr]
                                                           FROM     @tbl_invalid_identifier_start ) 
                SET @generated_class_name = '_' + @generated_class_name

            IF @generated_class_name IN ( SELECT    word
                                          FROM      @tbl_reserved_words ) 
                SET @generated_class_name = '_' + @generated_class_name
				                
			-- a place to store massaged identifiers
            DECLARE @tbl_identifier_cache TABLE ( [object_id] INT,
                                                  [original_object_schema] NVARCHAR(128),
                                                  [object_schema] NVARCHAR(128),
                                                  [object_name] NVARCHAR(128),
                                                  [original_object_name] NVARCHAR(128),
                                                  [orginal_column_name] NVARCHAR(128),
                                                  [object_column_name] NVARCHAR(128) )

            INSERT  INTO @tbl_identifier_cache ( [object_id], [original_object_schema], [object_schema], [object_name], [original_object_name] )
            VALUES  ( @current_object_id, @current_object_schema, @generated_class_namespace, @current_object_name, @generated_class_name )

-- TODO - @tbl_all_table_columns
-- massage all the properties
            DECLARE @fn_pascal_case_input_tmp NVARCHAR(128)
            DECLARE cCurrentColumn CURSOR READ_ONLY
                FOR SELECT  c.[name]
                    FROM    [sys].[all_columns] c
                    LEFT JOIN [sys].[types] t
                    ON      c.[user_type_id] = t.[user_type_id]
                    WHERE   c.[object_id] = @current_object_id -- system objects dupe sysname with nvarchar TODO: keep an eye on this as we merge this into the single column list                    
						-- AND NOT t.[name] = 'sysname'
                    ORDER BY c.[column_id]
            OPEN cCurrentColumn

            FETCH NEXT FROM cCurrentColumn INTO @fn_pascal_case_input_tmp 
            WHILE @@FETCH_STATUS = 0
                BEGIN
			
				-- perform mandatory identifier processing
                    SET @fn_pascal_case_input = @fn_pascal_case_input_tmp 
                    SET @fn_pascal_case_input = REPLACE(@fn_pascal_case_input, ' ', '_')
                    SET @fn_pascal_case_input = REPLACE(@fn_pascal_case_input, '.', '_')

					
					
                    SET @fn_pascal_case_output = @fn_pascal_case_input
				
                    IF @identifierMode = 'Pascal' 
                        BEGIN --> PascalCase Function common code
                            IF CHARINDEX(' ', @fn_pascal_case_input, 0) > 0
                                OR CHARINDEX('_', @fn_pascal_case_input, 0) > 0
											-- TSQL TIP: CS string comparisons on CI server
                                OR ( SELECT @fn_pascal_case_input COLLATE Latin1_General_CS_AS ) = UPPER(@fn_pascal_case_input)
                                OR ( SELECT @fn_pascal_case_input COLLATE Latin1_General_CS_AS ) = LOWER(@fn_pascal_case_input) 
                                BEGIN
                                    SET @fn_pascal_case_output = ''
                                    SET @fn_pascal_case_input = REPLACE(@fn_pascal_case_input, ' ', '_') ;
                                    SET @fn_pascal_case_input = REPLACE(@fn_pascal_case_input, '.', '_') ;


                                    DECLARE @tbl_pascal_case_parts_column TABLE ( [name] NVARCHAR(256) )
                                    DELETE  @tbl_pascal_case_parts_column ;
                                    
                                    WITH    Pieces ( pn, start, stop )
                                              AS ( SELECT   1, 1, CHARINDEX('_', @fn_pascal_case_input)
                                                   UNION ALL
                                                   SELECT   pn + 1, stop + 1, CHARINDEX('_', @fn_pascal_case_input, stop + 1)
                                                   FROM     Pieces
                                                   WHERE    stop > 0 )
                                        INSERT  INTO @tbl_pascal_case_parts_column
                                                SELECT  SUBSTRING(@fn_pascal_case_input, start, CASE WHEN stop > 0 THEN stop - start
                                                                                                     ELSE LEN(@fn_pascal_case_input)
                                                                                                END) AS s
                                                FROM    Pieces

                                    SET @fn_pascal_case_output = NULL
                                    SELECT  @fn_pascal_case_output = COALESCE(@fn_pascal_case_output + '', '') + ( UPPER(SUBSTRING([name], 1, 1)) + LOWER(SUBSTRING([name], 2, LEN([name]) - 1)) )
                                    FROM    @tbl_pascal_case_parts_column
                                END
                            ELSE 
                                SET @fn_pascal_case_output = UPPER(SUBSTRING(@fn_pascal_case_input, 1, 1)) + SUBSTRING(@fn_pascal_case_input, 2, LEN(@fn_pascal_case_input) - 1)
                        END
                        
                    IF SUBSTRING(@fn_pascal_case_output, 1, 1) IN ( SELECT  [chr]
                                                                    FROM    @tbl_invalid_identifier_start ) 
                        SET @fn_pascal_case_output = '_' + @fn_pascal_case_output

                    IF @fn_pascal_case_output IN ( SELECT   word
                                                   FROM     @tbl_reserved_words ) 
                        SET @fn_pascal_case_output = '_' + @fn_pascal_case_output
                        		                    
                    SET @generated_property_name = @fn_pascal_case_output
		                    
                    INSERT  INTO @tbl_identifier_cache ( [object_id], [original_object_schema], [object_schema], [original_object_name], [object_name], [orginal_column_name], [object_column_name] )
                    VALUES  ( @current_object_id, @current_object_schema, @generated_class_namespace, @current_object_name, @generated_class_name, @fn_pascal_case_input_tmp, @generated_property_name )
                    FETCH NEXT FROM cCurrentColumn INTO @fn_pascal_case_input_tmp
                END
            CLOSE cCurrentColumn
            DEALLOCATE cCurrentColumn
            FETCH NEXT FROM cCurrentObject INTO @current_object_id, @current_object_schema, @current_object_name, @current_object_type
        END
    CLOSE cCurrentObject
    
END
 
BEGIN -- render file header
    SET @output_chunk = @output_chunk + @nl 
    SET @output_chunk = @output_chunk + @nl + '// Generated by DeadSimpleDTO2'
    SET @output_chunk = @output_chunk + @nl + '// http://deadsimpledto.codeplex.com/'
    SET @output_chunk = @output_chunk + @nl + '// ' + CAST(GETDATE() AS NVARCHAR(128))
    SET @output_chunk = @output_chunk + @nl 
    IF @renderDomainObjects = 1
		BEGIN
			SET @output_chunk = @output_chunk + @nl + 'using System;'
			SET @output_chunk = @output_chunk + @nl + 'using System.Collections.Generic;'
			IF @renderDataContract = 1 
				SET @output_chunk = @output_chunk + @nl + 'using System.Runtime.Serialization;'
			IF @renderLoaderAttributes = 1 
				SET @output_chunk = @output_chunk + @nl + 'using System.Data.Linq.Mapping;'        
		        
			IF @renderINotify = 1 
				BEGIN
					SET @output_chunk = @output_chunk + @nl + 'using System.ComponentModel;'
					SET @output_chunk = @output_chunk + @nl + 'using System.Diagnostics;'
					SET @output_chunk = @output_chunk + @nl + 'using System.Reflection;'
				END
		END
    
        
        
    IF ISNULL(@namespacesToInclude, '') != '' 
        BEGIN -- injection of namespaces 
            DECLARE @tmp_tbl_namespaces TABLE ( ns NVARCHAR(256) ) ;
            WITH    Pieces ( pn, start, stop )
                      AS ( SELECT   1, 1, CHARINDEX(',', @namespacesToInclude)
                           UNION ALL
                           SELECT   pn + 1, stop + 1, CHARINDEX(',', @namespacesToInclude, stop + 1)
                           FROM     Pieces
                           WHERE    stop > 0 )
                INSERT  INTO @tmp_tbl_namespaces ( ns )
                        SELECT  SUBSTRING(@namespacesToInclude, start, CASE WHEN stop > 0 THEN stop - start
                                                                            ELSE LEN(@namespacesToInclude)
                                                                       END) AS s
                        FROM    Pieces
            SET @tmp_sql = NULL
            SELECT  @tmp_sql = COALESCE(@tmp_sql + '', '') + 'using ' + ns + ';' + @nl
            FROM    @tmp_tbl_namespaces
            SET @output_chunk = @output_chunk + @nl + @tmp_sql

        END     
		
    SET @output_chunk = @output_chunk + @nl + 'namespace ' + @rootNamespace
    SET @output_chunk = @output_chunk + @nl + '{'		
	
	--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    BEGIN -- output chunk
        IF @returnResultSet = 1 
            BEGIN
                INSERT  INTO @tbl_output_type ( [name], line )
                VALUES  ( 'NAMESPACE_OPEN', @output_chunk )
            END
        ELSE 
            PRINT @output_chunk
        SET @output_chunk = ''
    END
	--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
	
END


/*
//////////////////////////////////////
		Begin Outer Object Loop
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
*/
OPEN cCurrentObject
FETCH NEXT FROM cCurrentObject INTO @current_object_id, @current_object_schema, @current_object_name, @current_object_type

WHILE @@FETCH_STATUS = 0
    BEGIN
		-- fetch the object name from the identifier cache
        SELECT  @generated_class_namespace = object_schema, @generated_class_name = [object_name]
        FROM    @tbl_identifier_cache
        WHERE   [object_id] = @current_object_id
		

		-- clear member cache at the top of the cycle
        DELETE  FROM @tbl_member_cache


        -- add this type to the member cache in case it refers to itself ala northwind.employees
        INSERT  INTO @tbl_member_cache ( [name] ) VALUES  ( @generated_class_name )
        		
		
		BEGIN -- reset classmap table and build key parts
			DELETE  @tbl_tmp_class_map		
			INSERT  INTO @tbl_tmp_class_map ( [line] )
			VALUES  ( '	namespace ' + CASE @renderSchemaNamespace
											WHEN 1 THEN @generated_class_namespace + '.'
											ELSE ''
										  END + 'FluentClassMaps' )
			INSERT  INTO @tbl_tmp_class_map ( [line] )
			VALUES  ( '	{' )            			

			
			INSERT  INTO @tbl_tmp_class_map ( [line] )
			VALUES  ( '		public partial class ' + @generated_class_name + 'Map : ' + @rootNamespace + '.FluentClassMaps.ClassMapWrap<' + @generated_class_name + '>' )
			INSERT  INTO @tbl_tmp_class_map ( [line] )
			VALUES  ( '		{' )
			INSERT  INTO @tbl_tmp_class_map ( [line] )
			VALUES  ( '			public ' + @generated_class_name + 'Map()' )
			INSERT  INTO @tbl_tmp_class_map ( [line] )
			VALUES  ( '			{' )
			INSERT  INTO @tbl_tmp_class_map ( [line] )
			VALUES  ( '				PreMap();' )
			INSERT  INTO @tbl_tmp_class_map ( [line] )
			VALUES  ( '				Table("`' + @current_object_name + '`");' )
			INSERT  INTO @tbl_tmp_class_map ( [line] )
			VALUES  ( '				Schema("`' + @current_object_schema + '`");' )			
			If @eagerLoadEntities = 1
				BEGIN
					INSERT  INTO @tbl_tmp_class_map ( [line] )
					VALUES  ( '				Not.LazyLoad();' )							
				END
			
			BEGIN -- build the key mapping for fluent hnibernate

				-- first check to see if it is a composite key
				DECLARE @primary_key_count INT 
				SELECT  @primary_key_count = COUNT(*)
				FROM    @tbl_unique_key_columns
				WHERE   table_schema = @current_object_schema
						AND table_name = @current_object_name
						AND is_primary_key = 1

				IF @primary_key_count > 1 
					BEGIN
						INSERT  INTO @tbl_tmp_class_map ( [line] )
						VALUES  ( '				CompositeId()' )
	                    
						-- LIMITATION: composite keys apparently have no generator mapping in fn
						-- see nh docs, composit key objects are ther own keys
						SET @tmp_sql = NULL
						
						SELECT  @tmp_sql = COALESCE(@tmp_sql + '', '') + '.KeyProperty( x => x.' + ic.object_column_name + ', "`' + ukc.column_name + '`" )'
						FROM    @tbl_unique_key_columns ukc
						INNER JOIN @tbl_identifier_cache ic
						ON      ukc.table_schema = ic.original_object_schema
								AND ukc.table_name = ic.original_object_name
								AND ukc.column_name = ic.orginal_column_name
						WHERE   table_schema = @current_object_schema
								AND table_name = @current_object_name
								AND is_primary_key = 1
						ORDER BY column_id 
						SET @tmp_sql = @tmp_sql + ';'
						INSERT  INTO @tbl_tmp_class_map ( [line] )
						VALUES  ( '					' + @tmp_sql )
					END        
				IF @primary_key_count = 1 
					BEGIN

				   -- LIMITATION: only identity, and rowguid or newid() defaults are mapped
				   -- TODO: this is so very ugly. do the work, declare the variables and determine the type first
				   -- no, really TODO:REFACTOR THIS!
						
						SET @tmp_sql = NULL
						SELECT TOP 1
								@tmp_sql = COALESCE(@tmp_sql + '', '') 
								+ @nl + '				Id(x => x.' + ic.object_column_name + ')' 
								+ CASE ukc.is_identity
									WHEN 1 THEN '.GeneratedBy.Native()'
									ELSE
										CASE ukc.is_rowguidcol
										WHEN 1 THEN '.GeneratedBy.GuidComb()'
										ELSE 
											CASE CHARINDEX('newid', LOWER(ISNULL(ukc.default_definition, '')))
											WHEN 0 THEN '.GeneratedBy.Assigned()'
											ELSE '.GeneratedBy.GuidComb()'
											END
										END
									END 
									
									+ '.Column("`' + ukc.column_name + '`")' 
									+ @nl + '					.UnsavedValue(id.GetIdentityMapping().Type.GetUnderlyingSystemType().IsValueType ? System.Activator.CreateInstance(id.GetIdentityMapping().Type.GetUnderlyingSystemType()) : null);'
									
						FROM    @tbl_unique_key_columns ukc
						INNER JOIN @tbl_identifier_cache ic
						ON      ukc.table_schema = ic.original_object_schema
								AND ukc.table_name = ic.original_object_name
								AND ukc.column_name = ic.orginal_column_name
						WHERE   table_schema = @current_object_schema
								AND table_name = @current_object_name
								AND is_primary_key = 1

						INSERT  INTO @tbl_tmp_class_map ( [line] )
						VALUES  ( @tmp_sql )
	            
					END  
			END			
		END

        BEGIN -- render class open
			
			IF @renderSchemaNamespace = 1 
				BEGIN -- open the schema namespace if necessary
					SET @output_chunk = @output_chunk + @nl + '	namespace ' + @generated_class_namespace
					SET @output_chunk = @output_chunk + @nl + '	{'
				END


			-- render attributes
			IF @renderDataContract = 1 
				BEGIN
					--
					SET @output_chunk = @output_chunk + @nl + '			[DataContract('
					IF LEN(@dataContractNamespace) > 0 
						SET @output_chunk = @output_chunk + 'Namespace="' + @dataContractNamespace + CASE @renderSchemaNamespace
																									   WHEN 1 THEN '.' + @generated_class_namespace
																									   ELSE ''
																									 END + '",'
					SET @output_chunk = @output_chunk + 'Name="' + @generated_class_name + '")]'
				END

			IF @renderSerializable = 1 
				SET @output_chunk = @output_chunk + @nl + '		[Serializable]'
			IF @renderLoaderAttributes = 1 
				SET @output_chunk = @output_chunk + @nl + '		[Table(Name="[' + @current_object_schema + '].[' + @current_object_name + ']")]'


			
			-- render class declaration
			SET @output_chunk = @output_chunk + @nl + '		public partial class ' + @generated_class_name + CASE @renderCommonBase
																												   WHEN 1 THEN ' : ' + REPLACE(@commonBaseName, '$', @generated_class_name)
																												   ELSE ''
																												 END
			SET @output_chunk = @output_chunk + @nl + '		{'

			--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
			BEGIN -- output class header
				IF @returnResultSet = 1 and @renderDomainObjects = 1
					BEGIN
								
						INSERT  INTO @tbl_output_type ( [name], line )
						VALUES  ( CASE @renderSchemaNamespace
									WHEN 1 THEN @generated_class_namespace + '.'
									ELSE ''
								  END + @generated_class_name, NULL )
						INSERT  INTO @tbl_output_type ( [name], line )
						VALUES  ( NULL, @output_chunk )
					END
				ELSE 
					If @renderDomainObjects = 1
						PRINT @output_chunk
				SET @output_chunk = ''
			END
			--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
			
			BEGIN -- render #cTor
			
				-- NOTE: typically I would like to use camel cased input params but in order to ensure that reserved words
				-- are accounted for after the member name is camelcased would add yet another bunch of code to maintain
				-- for simply aesthetic value. So... you get originally cased input params
				DECLARE @assignable_key_ctor_sig NVARCHAR(MAX)
				SET @assignable_key_ctor_sig = NULL
				SELECT  @assignable_key_ctor_sig = COALESCE(@assignable_key_ctor_sig + ', ', '') + clr_type + ' ' + member_name
				FROM    @tbl_all_table_columns
				WHERE   [object_id] = @current_object_id
						AND is_primary_key = 1
						
				SET @output_chunk = @output_chunk + @nl + '			public ' + @generated_class_name + '()'
				SET @output_chunk = @output_chunk + @nl + '			{'
				SET @output_chunk = @output_chunk + @nl + '			}'	   
				
				IF ISNULL(@assignable_key_ctor_sig, '') != '' 
					BEGIN -- default ctor if assignable keys are present
						SET @output_chunk = @output_chunk + @nl + '			public ' + @generated_class_name + '(' + ISNULL(@assignable_key_ctor_sig, '') + ')'
						SET @output_chunk = @output_chunk + @nl + '			{'
						
						SET @tmp_sql = NULL
						SELECT  @tmp_sql = COALESCE(@tmp_sql + @nl, '') + '				_' + member_name_camel + ' = ' + member_name + ';'
						FROM    @tbl_all_table_columns
						WHERE   [object_id] = @current_object_id
								AND is_primary_key = 1

						SET @output_chunk = @output_chunk + @nl + ISNULL(@tmp_sql, '')
						SET @output_chunk = @output_chunk + @nl + '			}'
					END
					

				SET @output_chunk = @output_chunk + @nl 
	            SET @output_chunk = @output_chunk + @nl + '			#region Properties'
	            SET @output_chunk = @output_chunk + @nl 
	            
				--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
				BEGIN -- output chunk
					IF @returnResultSet = 1 AND @renderDomainObjects = 1
						BEGIN
							INSERT  INTO @tbl_output_type ( [name], line )
							VALUES  ( NULL, @output_chunk )
						END
					ELSE 
						IF @renderDomainObjects = 1
							PRINT @output_chunk
					SET @output_chunk = ''
				END
				--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<            
					
			END   			
		END			

        BEGIN -- inner column cursor
			
			
			
            DECLARE cProperties CURSOR READ_ONLY
                FOR SELECT  [object_id], table_schema, table_name, column_id, column_name, column_type, max_length, is_nullable, is_primary_key, is_foreign_key, is_rowguidcol, is_identity, is_computed, default_definition, clr_type, is_value_type, member_name, member_name_camel
                    FROM    @tbl_all_table_columns
                    WHERE   [object_id] = @current_object_id
                    ORDER BY [column_id]
			
            OPEN cProperties
            FETCH NEXT FROM cProperties INTO @ccol_object_id, @ccol_table_schema, @ccol_table_name, @ccol_column_id, @ccol_column_name, @ccol_column_type, @ccol_max_length, @ccol_is_nullable, @ccol_is_primary_key, @ccol_is_foreign_key, @ccol_is_rowguidcol, @ccol_is_identity, @ccol_is_computed, @ccol_default_definition, @ccol_clr_type, @ccol_is_value_type, @ccol_member_name, @ccol_member_name_camel 
            WHILE @@FETCH_STATUS = 0
                BEGIN
                
                
                
					BEGIN -- render column mapping
						IF @ccol_is_primary_key = 0 -- we should have already set up the key at the head of the loop.
							INSERT  INTO @tbl_tmp_class_map ( [line] )
							VALUES  ( '				Map(x => x.' + @ccol_member_name + ').Column("`' + @ccol_column_name + '`")' 
							+ CASE @ccol_is_computed
							   WHEN 1 THEN '.ReadOnly()'
							   ELSE ''
							 END + CASE @ccol_is_nullable
									 WHEN 0 THEN '.Not'
									 ELSE ''
								   END + '.Nullable()' 
							+ CASE CHARINDEX('NEWID', UPPER(ISNULL(@ccol_default_definition, '')))
														   WHEN 0 THEN ''
														   ELSE '.Generated.Insert()'
														 END 
														 + CASE CHARINDEX('GETDATE', UPPER(ISNULL(@ccol_default_definition, '')))
																 WHEN 0 THEN ''
																 ELSE '.Generated' + CASE CHARINDEX('MODIFIED', UPPER(ISNULL(@ccol_member_name, '')))
																					   WHEN 0 THEN '.Insert()'
																					   ELSE '.Always()'
																					 END
															   END + 
	                                                           
															   + CASE @ccol_is_foreign_key WHEN 1 THEN '.Not.Update().Not.Insert()' ELSE '' END
															   + ';' )						
					END
					
					                
					-- render backer - sorry, has to be adjacent to the property. i am not a magician
					SET @output_chunk = @output_chunk + @nl 
                    SET @output_chunk = @output_chunk + @nl + '			protected ' + @ccol_clr_type + ' _' + @ccol_member_name_camel + ';'
					SET @output_chunk = @output_chunk + @nl 
					SET @output_chunk = @output_chunk + @nl + '			/// <summary>'
					SET @output_chunk = @output_chunk + @nl + '			/// ' + CASE @ccol_is_nullable WHEN 0 THEN ' Required ' ELSE '' END + CASE @ccol_is_computed WHEN 1 THEN ' Computed ' ELSE '' END + CASE @ccol_is_primary_key WHEN 1 THEN ' PK ' ELSE '' END + CASE CHARINDEX('newid', LOWER(ISNULL(@ccol_default_definition, ''))) WHEN 0 THEN '' ELSE ' Generated ' END + CASE CHARINDEX('getdate', LOWER(ISNULL(@ccol_default_definition, ''))) WHEN 0 THEN '' ELSE ' Generated ' END+ CASE @ccol_is_identity WHEN 0 THEN '' ELSE ' Generated ' END
					SET @output_chunk = @output_chunk + @nl + '			/// </summary>'				
					
                    BEGIN --  render custom attributes

                        IF @renderDataContract = 1 
                            SET @output_chunk = @output_chunk + @nl + '			[DataMember]'
                            
                        IF @renderLoaderAttributes = 1 
                            BEGIN
								DECLARE @is_column_generated BIT 
								IF @ccol_is_computed  = 1 
									OR CHARINDEX('newid', LOWER(ISNULL(@ccol_default_definition, ''))) > 0 
									OR CHARINDEX('getdate', LOWER(ISNULL(@ccol_default_definition, ''))) > 0 
									OR @ccol_is_identity = 1
									SET @is_column_generated= 1
								ELSE
									SET @is_column_generated= 0
								
								SET @output_chunk = @output_chunk + @nl + '			[Column(Name = "' + @ccol_column_name 
								+ '", IsPrimaryKey = ' 
								+ CASE @ccol_is_primary_key
									WHEN 1 THEN 'true'
									ELSE 'false'
								END 
								+ ', IsDbGenerated = ' 
								+ CASE @is_column_generated
									WHEN 1 THEN 'true'
									ELSE 'false'
								END 
								+ ', CanBeNull = ' 
								+ CASE @ccol_is_nullable
									WHEN 1 THEN 'true'
									ELSE 'false'
									END + ', DbType = "' + @ccol_column_type + '")]'
                            END
                    END
                

 
				-- render getter/setter
					DECLARE @column_is_read_only BIT

					
					IF (@hideReadOnlySetters = 1 AND @ccol_is_identity =1 OR @ccol_is_computed =1) OR (@renderFluentNHibernate = 1 AND ((@ccol_is_primary_key = 0 and  @ccol_is_foreign_key = 1) OR @ccol_is_identity =1 OR @ccol_is_computed =1))
						SET @column_is_read_only=1
					ELSE
						SET @column_is_read_only=0
						

                    SET @output_chunk = @output_chunk + @nl + '			public virtual ' + @ccol_clr_type + ' ' + @ccol_member_name
                    SET @output_chunk = @output_chunk + @nl + '			{'
                    SET @output_chunk = @output_chunk + @nl + '				get { return _' + @ccol_member_name_camel + '; }'
                    SET @output_chunk = @output_chunk + @nl + '				'+ CASE @column_is_read_only WHEN 1 THEN 'private ' ELSE '' END +'set'
                    SET @output_chunk = @output_chunk + @nl + '				{'
                    IF @ccol_clr_type = 'Byte[]' 
                        BEGIN
                            IF @renderCommonBase = 1 
								SET @output_chunk = @output_chunk + @nl + '					if (ByteArrayCompare(value, _' + @ccol_member_name_camel + ')) return;'
                            ELSE 
                                SET @output_chunk = @output_chunk + @nl + '					// no Intrisic method for comparing byte arrays. Use base class option'
                        END
                    ELSE 
                        SET @output_chunk = @output_chunk + @nl + '					if (value == _' + @ccol_member_name_camel + ') return;'

                    SET @output_chunk = @output_chunk + @nl + '					_' + @ccol_member_name_camel + '= value;'
                    IF @renderINotify = 1 
                        SET @output_chunk = @output_chunk + @nl + '					RaisePropertyChanged("' + @ccol_member_name + '");'
                    SET @output_chunk = @output_chunk + @nl + '				}'
                    SET @output_chunk = @output_chunk + @nl + '			}'
                    
					--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                    BEGIN -- output chunk
                        IF @returnResultSet = 1 AND @renderDomainObjects = 1
                            BEGIN
                                INSERT  INTO @tbl_output_type ( [name], line )
                                VALUES  ( NULL, @output_chunk )
                            END
                        ELSE
							IF @renderDomainObjects = 1
								PRINT @output_chunk
                        SET @output_chunk = ''
                    END
					--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<                                

					
                    FETCH NEXT FROM cProperties INTO @ccol_object_id, @ccol_table_schema, @ccol_table_name, @ccol_column_id, @ccol_column_name, @ccol_column_type, @ccol_max_length, @ccol_is_nullable, @ccol_is_primary_key, @ccol_is_foreign_key, @ccol_is_rowguidcol, @ccol_is_identity, @ccol_is_computed, @ccol_default_definition, @ccol_clr_type, @ccol_is_value_type, @ccol_member_name, @ccol_member_name_camel 
                END
				
			
            CLOSE cProperties
            DEALLOCATE cProperties
        

            SET @output_chunk = @output_chunk + @nl + '			#endregion'
            SET @output_chunk = @output_chunk + @nl


        END

        
        
            BEGIN -- render relationships
				DECLARE @tbl_fkc TABLE ( column_name NVARCHAR(128) )
				DECLARE @tbl_pkc TABLE ( column_name NVARCHAR(128) )
                SET @output_chunk = @output_chunk + @nl + '			#region Relations'
                SET @output_chunk = @output_chunk + @nl 
                DECLARE @fk_name NVARCHAR(128),
                    @fk_schema NVARCHAR(128),
                    @fk_table NVARCHAR(128),
                    @fk_backer NVARCHAR(256),
                    @fk_property NVARCHAR(256),
                    @fk_gen_name NVARCHAR(128),
                    @fk_gen_schema NVARCHAR(128),
                    @fk_gen_prop NVARCHAR(256),
                    @fk_gen_type NVARCHAR(128),
                    @fn_fk_is_singular BIT,
                    @pk_schema NVARCHAR(128),
                    @pk_name NVARCHAR(128)

		       
                IF @renderManyToOneRelations = 1 
                    BEGIN
                        DECLARE cForeignKeys CURSOR READ_ONLY
                            FOR SELECT  DISTINCT
                                        k1.base_schema_name, k1.constraint_name, k1.unique_schema_name, k1.unique_table_name,k1.base_table_name 
                                FROM    @tbl_foreign_key_columns k1
                                WHERE   k1.base_schema_name = @current_object_schema
                                        AND k1.base_table_name = @current_object_name
                                        AND EXISTS ( SELECT *
                                                     FROM   @tbl_objects_to_render
                                                     WHERE  [schema] = k1.base_schema_name
                                                            AND [name] = k1.base_table_name )
                                        AND EXISTS ( SELECT *
                                                     FROM   @tbl_objects_to_render
                                                     WHERE  [schema] = k1.unique_schema_name
                                                            AND [name] = k1.unique_table_name )

                        OPEN cForeignKeys
                        FETCH NEXT FROM cForeignKeys INTO @fk_schema, @fk_name, @pk_schema, @pk_name,@fk_table
                        WHILE @@FETCH_STATUS = 0
                            BEGIN
								-- render relationship
								-- check to see if any part has been excluded

                                IF EXISTS ( SELECT  *
                                            FROM    @tbl_members_to_exclude
                                            WHERE   member_name = ( @current_object_schema + '.' + @current_object_name + '+' + @fk_schema + '.' + @fk_name )
                                                    OR ( member_name = @fk_schema + '.' + @fk_name ) ) 
                                    BEGIN
                                        SET @output_chunk = @output_chunk + @nl + '			// dropped ' + @fk_schema + '.' + @fk_name
                                        GOTO LBL_NEXT_MANY_TO_ONE
                                    END

								-- fetch the object name from the identifier cache
                                SELECT  @fk_gen_schema = [object_schema], @fk_gen_name = [object_name]
                                FROM    @tbl_identifier_cache
                                WHERE   original_object_schema = @pk_schema
                                        AND original_object_name = @pk_name

								-- ensure no collisions by appending an incrementing number to duplicate identifiers in a type
                                SELECT  @members_count = COUNT(*)
                                FROM    @tbl_member_cache
                                WHERE   [name] = @fk_gen_name

								-- this is really hinky - clarify
                                IF @members_count = 0 
                                    SET @fk_property = @fk_gen_name
                                ELSE 
                                    SET @fk_property = @fk_gen_name + CAST(@members_count AS NVARCHAR(2))

                                INSERT  INTO @tbl_member_cache ( [name] )
                                VALUES  ( @fk_property )
                                INSERT  INTO @tbl_member_cache ( [name] )
                                VALUES  ( @fk_gen_name )

                                -- let the member cache happen so that we don't have walking identifiers and if
                                -- a substitution is to be made, just make it. Is up to the coder to ensure no collisions
                                -- happen

                                SET @substitution = NULL
                                SELECT  @substitution = substitution
                                FROM    @tbl_substitutions
                                WHERE   substitution LIKE @current_object_schema + '.' + @current_object_name + '.' + @fk_property + '%'
                                IF ISNULL(@substitution, '') != '' 
                                    BEGIN

                                        SET @substitution = SUBSTRING(@substitution, CHARINDEX('|', @substitution) + 1, LEN(@substitution) - CHARINDEX('|', @substitution))
                                        SET @fk_property = @substitution
                                    END


                                

							  -- camelCase the PascalCased property name to get a nice backer name
                                SET @fk_backer = '_' + LOWER(SUBSTRING(@fk_property, 1, 1)) + SUBSTRING(@fk_property, 2, LEN(@fk_property) - 1)



                                SET @fk_gen_name = @rootNamespace + '.' + CASE @renderSchemaNamespace
                                                                            WHEN 1 THEN @fk_gen_schema + '.'
                                                                            ELSE ''
                                                                          END + @fk_gen_name

                                SET @output_chunk = @output_chunk + @nl 
                                SET @output_chunk = @output_chunk + @nl + '			protected ' + @fk_gen_name + ' ' + @fk_backer + ';'
                                SET @output_chunk = @output_chunk + @nl 

								SET @output_chunk = @output_chunk + @nl +'			/// <summary>'
								SET @output_chunk = @output_chunk + @nl +'			/// ManyToOne On ' + @fk_schema + '.' + @fk_name
								SET @output_chunk = @output_chunk + @nl +'			/// </summary>'
                                IF @renderDataContract = 1 
                                    SET @output_chunk = @output_chunk + @nl + '			[DataMember]'
								IF @renderLoaderAttributes = 1
									BEGIN
										SELECT @this_key = NULL, @other_key = NULL
			
										SELECT @other_key = COALESCE(@other_key+ ',','') + unique_column_name FROM @tbl_foreign_key_columns WHERE base_schema_name = @fk_schema AND constraint_name = @fk_name
										SELECT @this_key  = COALESCE(@this_key + ',','') + base_column_name FROM @tbl_foreign_key_columns WHERE base_schema_name = @fk_schema AND constraint_name = @fk_name																	
										SET @output_chunk = @output_chunk + @nl + '			[Association(Name = "' + @fk_schema + '.' + @fk_name + '", ThisKey = "' + @this_key + '", OtherKey = "' + @other_key + '", IsForeignKey = true)]'
										
									END

                                SET @output_chunk = @output_chunk + @nl + '			public virtual ' + @fk_gen_name + ' ' + @fk_property
                                SET @output_chunk = @output_chunk + @nl + '			{'
                                SET @output_chunk = @output_chunk + @nl + '				get { return ' + @fk_backer + '; }'
                                SET @output_chunk = @output_chunk + @nl + '				set'
                                SET @output_chunk = @output_chunk + @nl + '				{'
                                SET @output_chunk = @output_chunk + @nl + '					if ( ReferenceEquals(value,' + @fk_backer + '))'
                                SET @output_chunk = @output_chunk + @nl + '						return;'
                                SET @output_chunk = @output_chunk + @nl + '					' + @fk_backer + '= value;'
                                IF @renderINotify = 1 
                                    SET @output_chunk = @output_chunk + @nl + '					RaisePropertyChanged("' + @fk_property + '");'
                                SET @output_chunk = @output_chunk + @nl + '				}'
                                SET @output_chunk = @output_chunk + @nl + '			}'
                                SET @output_chunk = @output_chunk + @nl 


								-- set the reference in the class map
								
								-- http://ayende.com/Blog/archive/2006/12/02/NHibernateCascadesTheDifferentBetweenAllAlldeleteorphansAndSaveupdate.aspx
								-- see this and the docs and get to know the nhibernate cascade options and how they interact with the db cascade options
					

								BEGIN -- render class map
									-- match the columns from both PK and fields from the FK. If all are same is 1:1
									SELECT @count1 = COUNT(*) FROM @tbl_foreign_key_columns fkc 
									FULL OUTER JOIN @tbl_unique_key_columns pkc  ON fkc.base_schema_name = pkc.table_schema  AND pkc.table_name = fkc.base_table_name
									FULL OUTER JOIN @tbl_unique_key_columns pkc2 ON fkc.unique_schema_name = pkc2.table_schema  AND pkc2.table_name = fkc.unique_table_name
									FULL OUTER JOIN @tbl_foreign_key_columns fkc2 ON fkc2.unique_schema_name = pkc2.table_schema  AND pkc2.table_name = fkc2.unique_table_name
									WHERE fkc.constraint_name = @fk_name AND ((fkc.base_column_name <>pkc.column_name) or(pkc2.column_name <> fkc2.unique_column_name))
									IF @count1 =0 
										BEGIN 
											INSERT  INTO @tbl_tmp_class_map ( [line] )
											VALUES  ( '				HasOne(x => x.' + @fk_property + ');') 
										END
									ELSE
										BEGIN 
											SET @tmp_sql = NULL

											-- we can only handle single column fk so that makes things a bit easier.
											-- count base side (this side) primary keys that contain the fk column, 
											-- include the unique side pk IF unique side is this side e.g. self referencing table
											-- if it is none, then we use the referece member for persistence
											
											-- are this fk columns(s) also in this pk columns(s)?
											SELECT  @count1 = COUNT(*)
											FROM    @tbl_unique_key_columns pkc
											WHERE   pkc.column_name IN ( SELECT unique_column_name
																		 FROM   @tbl_foreign_key_columns
																		 WHERE  [@tbl_foreign_key_columns].base_schema_name = @fk_schema
																				AND [@tbl_foreign_key_columns].constraint_name = @fk_name
																				AND [@tbl_foreign_key_columns].base_table_name = @fk_table )
													AND pkc.table_schema = @current_object_schema
													AND pkc.table_name = @current_object_name
													
											-- and then check for self reference
											IF @fk_table = @pk_name AND @fk_schema = @pk_schema
												SET @count1 = 0												
																					
											SELECT  @tmp_sql = COALESCE(@tmp_sql + ',', '') + '"`' + base_column_name + '`"'
											FROM    @tbl_foreign_key_columns
											WHERE   constraint_name = @fk_name
													AND base_schema_name = @fk_schema											
													
											INSERT  INTO @tbl_tmp_class_map ( [line] )
											VALUES  ( '				References(x => x.' + @fk_property + ').Columns(' + @tmp_sql + ')' 
											+ 
											CASE @count1 
												WHEN 0 THEN ''
														ELSE '.Not.Update().Not.Insert()'
													END
											+ CASE @eagerLoadRelations WHEN 1 THEN '.Not.LazyLoad()' ELSE '' END
											+ ';' )									
										END
								END
	


									
  								--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                                BEGIN -- output chunk
                                    IF @returnResultSet = 1 AND @renderDomainObjects = 1
                                        BEGIN
                                            INSERT  INTO @tbl_output_type ( [name], line )
                                            VALUES  ( NULL, @output_chunk )
                                        END
                                    ELSE 
										IF @renderDomainObjects = 1
											PRINT @output_chunk
                                    SET @output_chunk = ''
                                END
                                --<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                                
                                LBL_NEXT_MANY_TO_ONE:
                                FETCH NEXT FROM cForeignKeys INTO @fk_schema, @fk_name, @pk_schema, @pk_name,@fk_table
                            END

                        CLOSE cForeignKeys
                        DEALLOCATE cForeignKeys
                    END

				-- one to many relationships

                SET @fn_fk_is_singular = 0

                DECLARE cForeignKeys CURSOR READ_ONLY
                    FOR SELECT  DISTINCT
                                k1.base_schema_name, k1.constraint_name, k1.base_schema_name, k1.base_table_name
                        FROM    @tbl_foreign_key_columns k1
                        WHERE   k1.unique_schema_name = @current_object_schema
                                AND k1.unique_table_name = @current_object_name
                                AND EXISTS ( SELECT *
                                             FROM   @tbl_objects_to_render
                                             WHERE  [schema] = k1.base_schema_name
                                                    AND [name] = k1.base_table_name )
                                AND EXISTS ( SELECT *
                                             FROM   @tbl_objects_to_render
                                             WHERE  [schema] = k1.unique_schema_name
                                                    AND [name] = k1.unique_table_name )

                OPEN cForeignKeys
                FETCH NEXT FROM cForeignKeys INTO @fk_schema, @fk_name, @pk_schema, @pk_name
                WHILE @@FETCH_STATUS = 0
                    BEGIN
                        IF EXISTS ( SELECT  *
                                    FROM    @tbl_members_to_exclude
                                    WHERE   member_name = ( @current_object_schema + '.' + @current_object_name + '+' + @fk_schema + '.' + @fk_name )
                                            OR ( member_name = @fk_schema + '.' + @fk_name ) ) 
                            BEGIN
                                SET @output_chunk = @output_chunk + @nl + '			// dropped ' + @fk_schema + '.' + @fk_name
                                GOTO LBL_NEXT_ONE_TO_MANY
                            END

                        --SET @is_junction_table = 0 
                        --SET @junction_has_data = 0


						-- get the columns in the base side of the FK constraint

                        DELETE  FROM @tbl_fkc
                        INSERT  INTO @tbl_fkc ( column_name )
                                SELECT  base_column_name
                                FROM    @tbl_foreign_key_columns
                                WHERE   constraint_name = @fk_name




                        DECLARE @uk_schema_name NVARCHAR(128),
                            @uk_table_name NVARCHAR(128),
                            @uk_index_name NVARCHAR(128)



                        DECLARE cKey CURSOR READ_ONLY
                            FOR SELECT  DISTINCT
                                        table_schema, table_name, index_name
                                FROM    @tbl_unique_key_columns
                                WHERE   table_schema = @pk_schema
                                        AND table_name = @pk_name
                        OPEN cKey
                        FETCH NEXT FROM cKey INTO @uk_schema_name, @uk_table_name, @uk_index_name
                        WHILE @@FETCH_STATUS = 0
                            BEGIN
							-- get the unique constraint columns
                                
                                DELETE  FROM @tbl_pkc

                                INSERT  INTO @tbl_pkc ( column_name )
                                        SELECT  column_name
                                        FROM    @tbl_unique_key_columns
                                        WHERE   table_schema = @uk_schema_name
                                                AND table_name = @uk_table_name
                                                AND index_name = @uk_index_name

							-- if count is same and columns are same is 1:?

                                SELECT  @count2 = COUNT(*)
                                FROM    @tbl_pkc

							-- select all from both on name and exclude mismatches
                                SELECT  @count1 = COUNT(*)
                                FROM    @tbl_fkc F
                                FULL OUTER JOIN @tbl_pkc P
                                ON      f.column_name = p.column_name
                                WHERE   NOT p.column_name IS NULL
                                        AND NOT f.column_name IS NULL

                                IF @count1 = @count2 
                                    BEGIN
									-- the base side of the fk constraint corresponds exactly to
									-- at least on unique constraint making it effectively 1:?
                                        SET @fn_fk_is_singular = 1
                                        BREAK
                                    END


                                IF @renderManyToManyRelations = 1 
                                    BEGIN
									-- not 1:? - check for many-to-many
									-- if the base side of the FK constraint fits into ONE of the base table's unique constraints but the unique constraint
									-- has more columns then it is a many-to-one (from the perspective of the unique side) and may be many-to-many relationship
									-- that needs to be surfaced and the reference will be plural in both cases (e.g. junction records or related records)
									-- if the base side table consists solely of keys then it is definitely a many-to-many.
									-- if the base side has data fields as well as other FK constraints then it should be surfaced along with the related table.

									-- given the above lets first check to see if the FK will fit into the UT PK

                                        SELECT  @count1 = COUNT(*)
                                        FROM    @tbl_pkc P
                                        FULL OUTER JOIN @tbl_fkc F
                                        ON      f.column_name = p.column_name
                                        WHERE   NOT p.column_name IS NULL


                                        IF @count1 != 0 
                                            BEGIN -- the fk fits into the pk
                                                DECLARE @many_to_many_fk_schema NVARCHAR(128),
                                                    @many_to_many_fk_name NVARCHAR(128),
                                                    @many_to_many_fk_table_schema NVARCHAR(128),
                                                    @many_to_many_fk_table_name NVARCHAR(128)
											-- get the pk columns that are not in the fk and look for another fk
                                                DECLARE @tbl_opkc TABLE ( column_name NVARCHAR(128) )
                                                DELETE  FROM @tbl_opkc
                                                INSERT  INTO @tbl_opkc ( column_name )
                                                        SELECT  p.column_name
                                                        FROM    @tbl_pkc P
                                                        FULL OUTER JOIN @tbl_fkc F
                                                        ON      f.column_name = p.column_name
                                                        WHERE   f.column_name IS NULL


											-- find the fk constraints that contains the columns in @tbl_opkc and render a collection property for each
                                                DECLARE @tbl_mtmfk TABLE ( many_to_many_fk_schema NVARCHAR(128),
                                                                           many_to_many_fk_name NVARCHAR(128),
                                                                           many_to_many_fk_table_schema NVARCHAR(128),
                                                                           many_to_many_fk_table_name NVARCHAR(128) )
                                                DELETE  FROM @tbl_mtmfk

                                                INSERT  INTO @tbl_mtmfk ( many_to_many_fk_schema, many_to_many_fk_name, many_to_many_fk_table_schema, many_to_many_fk_table_name )
                                                        SELECT DISTINCT
                                                                fkc.base_schema_name, constraint_name, fkc.unique_schema_name, fkc.unique_table_name
                                                        FROM    @tbl_foreign_key_columns fkc
                                                        FULL OUTER JOIN @tbl_opkc o
                                                        ON      fkc.base_column_name = o.column_name
                                                        WHERE   fkc.base_schema_name = @pk_schema
                                                                AND fkc.base_table_name = @pk_name
                                                                AND NOT o.column_name IS NULL

                                                SELECT  @count1 = COUNT(*)
                                                FROM    @tbl_mtmfk
                                                IF @count1 != 0 
                                                    BEGIN
														--IF @count1 > 1
														--	SET @is_junction_table = 1
															
                                                        DECLARE cManyToManyFK CURSOR READ_ONLY
                                                            FOR SELECT  many_to_many_fk_schema, many_to_many_fk_name, many_to_many_fk_table_schema, many_to_many_fk_table_name
                                                                FROM    @tbl_mtmfk
                                                        OPEN cManyToManyFK
                                                        FETCH NEXT FROM cManyToManyFK INTO @many_to_many_fk_schema, @many_to_many_fk_name, @many_to_many_fk_table_schema, @many_to_many_fk_table_name
                                                        WHILE @@FETCH_STATUS = 0
                                                            BEGIN
															-- is many to many - render a collection

																-- fetch the object name from the identifier cache
                                                                SELECT  @fk_gen_schema = [object_schema], @fk_gen_name = [object_name]
                                                                FROM    @tbl_identifier_cache
                                                                WHERE   original_object_schema = @many_to_many_fk_table_schema
                                                                        AND original_object_name = @many_to_many_fk_table_name
                                                                        
                                                               
																-- ensure no collisions by appending an incrementing number to duplicate identifiers in a type
                                                                SELECT  @members_count = COUNT(*)
                                                                FROM    @tbl_member_cache
                                                                WHERE   [name] = @fk_gen_name

																---- this is really hinky - clarify

                                                                IF @members_count = 0 
                                                                    SET @fk_property = @fk_gen_name
                                                                ELSE 
                                                                    SET @fk_property = @fk_gen_name + CAST(@members_count AS NVARCHAR(2))

                                                                INSERT  INTO @tbl_member_cache ( [name] )
                                                                VALUES  ( @fk_property )
                                                                INSERT  INTO @tbl_member_cache ( [name] )
                                                                VALUES  ( @fk_gen_name )

																-- let the member cache happen so that we don't have walking identifiers and if
																-- a substitution is to be made, just make it. Is up to the coder to ensure no collisions
																-- happen

                                                                SET @substitution = NULL
                                                                SELECT  @substitution = substitution
                                                                FROM    @tbl_substitutions
                                                                WHERE   substitution LIKE @current_object_schema + '.' + @current_object_name + '.' + @fk_property + '%'
                                                                IF ISNULL(@substitution, '') != '' 
                                                                    BEGIN

                                                                        SET @substitution = SUBSTRING(@substitution, CHARINDEX('|', @substitution) + 1, LEN(@substitution) - CHARINDEX('|', @substitution))


                                                                        SET @output_chunk = @output_chunk + @nl + '// SUBSTITUTE 2' + @substitution + ' FOR ' + @current_object_schema + '.' + @current_object_name + '.' + @fk_property + '%'
                                                                        SET @fk_property = @substitution
                                                                    END

                                                                
																
																
																-- camelCase the PascalCased property name to get a nice backer name
                                                                SET @fk_backer = '_' + LOWER(SUBSTRING(@fk_property, 1, 1)) + SUBSTRING(@fk_property, 2, LEN(@fk_property) - 1)

                                                                SET @fk_gen_name = CASE @renderSchemaNamespace
                                                                                     WHEN 1 THEN @fk_gen_schema + '.'
                                                                                     ELSE ''
                                                                                   END + @fk_gen_name

                                                                
                                                                SET @fk_gen_type = @listInstanceType + '<' + @rootNamespace + '.' + @fk_gen_name + '>'	
                                                                SET @fk_gen_prop = @listMemberType + '<' + @rootNamespace + '.' + @fk_gen_name + '>'											
                                                                


															-- TODONE: put a guard in here to prevent rendering of a faulty map if either of the FKs have multiple columns
															-- because it does not seem there is a way to map it. Should build some tables and try just doubling up on the parent and childkey columns
															-- TODONE: figure out an extension point so that the user can add the .inverse as there is no way for me to determine the
															-- appropriate place for it.
															
															--NOTE: You MUST define the Inverse side of the M:N yourself. There is no way for this script to make that decision.
															
                                                                SELECT  @count1 = COUNT(*)
                                                                FROM    @tbl_fkc
                                                                SELECT  @count2 = COUNT(*)
                                                                FROM    @tbl_foreign_key_columns
                                                                WHERE   base_schema_name = @many_to_many_fk_schema
                                                                        AND constraint_name = @many_to_many_fk_name
                                                                        
                                                                        
                                                                IF @count1 = 1 AND @count2 = 1 
                                                                    BEGIN
                                                                        INSERT  INTO @tbl_tmp_class_map ( [line] )
                                                                        VALUES  ( '				HasManyToMany(x => x.' + @fk_property + ').Cascade.SaveUpdate()' 
                                                                        + 
                                                                        CASE @listMemberType
																			WHEN 'IList' THEN '.AsBag()'
																			WHEN 'ISet' THEN '.AsSet()'
																			ELSE '' 
																		END
                                                                         + '.Schema("`' + @pk_schema + '`")' + '.Table("`' + @pk_name + '`")' + '.ParentKeyColumn("`' 
                                                                         + ( SELECT TOP 1 column_name FROM @tbl_fkc ) + '`")' 
                                                                         + '.ChildKeyColumn("`' + ( SELECT TOP (1) base_column_name
																									FROM   @tbl_foreign_key_columns
																									WHERE  base_schema_name = @many_to_many_fk_schema
																										AND constraint_name = @many_to_many_fk_name ) + '`")'
																										+ CASE @eagerLoadRelations WHEN 1 THEN '.Not.LazyLoad()' ELSE '' END + ';' )
                                                                    END
                                                                ELSE
																	BEGIN
																		INSERT  INTO @tbl_tmp_class_map ( [line] )
																		VALUES  ( '// cannot render m:n on ' + @pk_schema + '.' + @pk_name + ' because there are too many columns in the key ' )																	
																	END
																	


                                                                SET @output_chunk = @output_chunk + @nl 
                                                                SET @output_chunk = @output_chunk + @nl + '			protected ' + @fk_gen_prop + ' ' + @fk_backer + ' = new ' + @fk_gen_type + '();'                                         
                                                                SET @output_chunk = @output_chunk + @nl 
                                                                                   
																SET @output_chunk = @output_chunk + @nl +'			/// <summary>'
																SET @output_chunk = @output_chunk + @nl +'			/// ManyToMany On ' + @fk_schema + '.' + @fk_name
																SET @output_chunk = @output_chunk + @nl +'			/// </summary>'
                                                                IF @renderDataContract = 1 
                                                                    SET @output_chunk = @output_chunk + @nl + '			[DataMember]'
																IF @renderLoaderAttributes = 1
																	BEGIN
																		SELECT @this_key = NULL, @other_key = NULL
											
																		SELECT @this_key  = COALESCE(@this_key + ',','') + unique_column_name FROM @tbl_foreign_key_columns WHERE base_schema_name = @fk_schema AND constraint_name = @fk_name
																		SELECT @other_key = COALESCE(@other_key + ',','') + base_column_name FROM @tbl_foreign_key_columns WHERE base_schema_name = @fk_schema AND constraint_name = @fk_name																	
																		SET @output_chunk = @output_chunk + @nl + '			[Association(Name = "' + @fk_schema + '.' + @fk_name + '", ThisKey = "' + @this_key + '", OtherKey = "' + @other_key + '", IsForeignKey = false, Storage="ManyToMany")]'
																		
																	END                                                                    
                                                                SET @output_chunk = @output_chunk + @nl + '			public virtual ' + @fk_gen_prop + ' ' + @fk_property

                                                                SET @output_chunk = @output_chunk + @nl + '			{'
                                                                SET @output_chunk = @output_chunk + @nl + '				get { return ' + @fk_backer + '; }'

																

                                                                SET @output_chunk = @output_chunk + @nl + '				private set'
                                                                SET @output_chunk = @output_chunk + @nl + '				{'
                                                                SET @output_chunk = @output_chunk + @nl + '					if ( ReferenceEquals(value,' + @fk_backer + '))'
                                                                SET @output_chunk = @output_chunk + @nl + '						return;'
                                                                SET @output_chunk = @output_chunk + @nl + '					' + @fk_backer + '= value;'
                                                                IF @renderINotify = 1 
                                                                    SET @output_chunk = @output_chunk + @nl + '					RaisePropertyChanged("' + @fk_property + '");'
                                                                SET @output_chunk = @output_chunk + @nl + '				}'

                                                                SET @output_chunk = @output_chunk + @nl + '			}'
                                                                SET @output_chunk = @output_chunk + @nl 

																--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                                                                BEGIN -- output chunk
                                                                    IF @returnResultSet = 1 AND @renderDomainObjects = 1
                                                                        BEGIN
                                                                            INSERT  INTO @tbl_output_type ( [name], line )
                                                                            VALUES  ( NULL, @output_chunk )
                                                                        END
                                                                    ELSE 
																		IF @renderDomainObjects = 1
																			PRINT @output_chunk
                                                                    SET @output_chunk = ''
                                                                END
                                                                --<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


                                                                FETCH NEXT FROM cManyToManyFK INTO @many_to_many_fk_schema, @many_to_many_fk_name, @many_to_many_fk_table_schema, @many_to_many_fk_table_name
                                                            END
                                                        CLOSE cManyToManyFK
                                                        DEALLOCATE cManyToManyFK
                                                        BREAK
                                                    END
                                            END
                                    END


                                FETCH NEXT FROM cKey INTO @uk_schema_name, @uk_table_name, @uk_index_name
                            END
                        CLOSE cKey
                        DEALLOCATE cKey

						IF @renderOneToManyRelations = 1 
                            BEGIN
								-- fetch the object name from the identifier cache
                                SELECT  @fk_gen_schema = [object_schema], @fk_gen_name = [object_name]
                                FROM    @tbl_identifier_cache
                                WHERE   original_object_schema = @pk_schema
                                        AND original_object_name = @pk_name



			                            --@tbl_substitutions
								-- ensure no collisions by appending an incrementing number to duplicate identifiers in a type
                                SELECT  @members_count = COUNT(*)
                                FROM    @tbl_member_cache
                                WHERE   [name] = @fk_gen_name

								-- this is really hinky - clarify
                                IF @members_count = 0 
                                    SET @fk_property = @fk_gen_name
                                ELSE 
                                    SET @fk_property = @fk_gen_name + CAST(@members_count AS NVARCHAR(2))

                                INSERT  INTO @tbl_member_cache ( [name] )
                                VALUES  ( @fk_property )
                                INSERT  INTO @tbl_member_cache ( [name] )
                                VALUES  ( @fk_gen_name )

                                -- let the member cache happen so that we don't have walking identifiers and if
                                -- a substitution is to be made, just make it. Is up to the coder to ensure no collisions
                                -- happen


                                SET @substitution = NULL
                                SELECT  @substitution = substitution
                                FROM    @tbl_substitutions
                                WHERE   substitution LIKE @current_object_schema + '.' + @current_object_name + '.' + @fk_property + '%'
                                IF ISNULL(@substitution, '') != '' 
                                    BEGIN

                                        SET @substitution = SUBSTRING(@substitution, CHARINDEX('|', @substitution) + 1, LEN(@substitution) - CHARINDEX('|', @substitution))


                                        SET @output_chunk = @output_chunk + @nl + '// SUBSTITUTE 1' + @substitution + ' FOR ' + @current_object_schema + '.' + @current_object_name + '.' + @fk_property + '%'
                                        SET @fk_property = @substitution
                                    END



                                

							  -- camelCase the PascalCased property name to get a nice backer name
                                SET @fk_backer = '_' + LOWER(SUBSTRING(@fk_property, 1, 1)) + SUBSTRING(@fk_property, 2, LEN(@fk_property) - 1)

                                SET @fk_gen_name = CASE @renderSchemaNamespace
                                                     WHEN 1 THEN @fk_gen_schema + '.'
                                                     ELSE ''
                                                   END + @fk_gen_name


                                IF @fn_fk_is_singular = 1 
                                    BEGIN
                                        SET @fk_gen_type = @rootNamespace + '.' + @fk_gen_name
                                        SET @fk_gen_prop = @rootNamespace + '.' + @fk_gen_name							
                                        SET @tmp_sql = NULL
                                        SELECT  @tmp_sql = COALESCE(@tmp_sql + ',', '') + '"`' + base_column_name + '`"'
                                        FROM    @tbl_foreign_key_columns
                                        WHERE   constraint_name = @fk_name
                                                AND base_schema_name = @fk_schema
                                                
                                        INSERT  INTO @tbl_tmp_class_map ( [line] )
                                        VALUES  ( '				HasOne(x => x.' + @fk_property + ');' )
                                    END
                                ELSE 
                                    BEGIN
                                        SET @fk_gen_type = @listInstanceType + '<' + @rootNamespace + '.' + @fk_gen_name + '>'	
                                        SET @fk_gen_prop = @listMemberType + '<' + @rootNamespace + '.' + @fk_gen_name + '>'	
										
                                        SET @tmp_sql = NULL
                                        SELECT  @tmp_sql = COALESCE(@tmp_sql + ',', '') + '"`' + base_column_name + '`"'
                                        FROM    @tbl_foreign_key_columns
                                        WHERE   constraint_name = @fk_name
                                                AND base_schema_name = @fk_schema

							                                        INSERT  INTO @tbl_tmp_class_map ( [line] )
                                        VALUES  ( '				HasMany(x => x.' + @fk_property + ').Inverse().Cascade.SaveUpdate().KeyColumns.Add(' + @tmp_sql + ')'+ CASE @eagerLoadRelations WHEN 1 THEN '.Not.LazyLoad()' ELSE '' END + ';' )
                                    END
                                       
                                SET @output_chunk = @output_chunk + @nl 
                                SET @output_chunk = @output_chunk + @nl + '			protected ' + @fk_gen_prop + ' ' + @fk_backer + CASE @fn_fk_is_singular
                                                                                                                                          WHEN 0 THEN ' = new ' + @fk_gen_type + '()'
                                                                                                                                          ELSE ''
                                                                                                                                        END + ';'
								SET @output_chunk = @output_chunk + @nl 
								SET @output_chunk = @output_chunk + @nl +'			/// <summary>'
								SET @output_chunk = @output_chunk + @nl +'			/// ' + CASE @fn_fk_is_singular WHEN 1 THEN 'OneToOne ' ELSE 'OneToMany ' END +'On ' + @fk_schema + '.' + @fk_name
								SET @output_chunk = @output_chunk + @nl +'			/// </summary>'                                
                                IF @renderDataContract = 1 
                                    SET @output_chunk = @output_chunk + @nl + '			[DataMember]'
								IF @renderLoaderAttributes = 1
									BEGIN
										
										SELECT @this_key = NULL, @other_key = NULL
										
										SELECT @this_key = COALESCE(@this_key + ',','') + unique_column_name FROM @tbl_foreign_key_columns WHERE base_schema_name = @fk_schema AND constraint_name = @fk_name
										SELECT @other_key = COALESCE(@other_key + ',','') + base_column_name FROM @tbl_foreign_key_columns WHERE base_schema_name = @fk_schema AND constraint_name = @fk_name
										
										SET @output_chunk = @output_chunk + @nl + '			[Association(Name = "' + @fk_schema + '.' + @fk_name + '", ThisKey = "' + @this_key + '", OtherKey = "' + @other_key + '", IsForeignKey = false)]'
										
									END                                    
                                SET @output_chunk = @output_chunk + @nl + '			public virtual ' + @fk_gen_prop + ' ' + @fk_property

                                SET @output_chunk = @output_chunk + @nl + '			{'
                                SET @output_chunk = @output_chunk + @nl + '				get { return ' + @fk_backer + '; }'

								-- NOTE: setters are required by default wcf serialization. remove at own peril

                                SET @output_chunk = @output_chunk + @nl + '				' + CASE @fn_fk_is_singular WHEN 0 THEN 'private ' ELSE '' END + 'set'
                                SET @output_chunk = @output_chunk + @nl + '				{'
                                SET @output_chunk = @output_chunk + @nl + '					if ( ReferenceEquals(value,' + @fk_backer + '))'
                                SET @output_chunk = @output_chunk + @nl + '						return;'
                                SET @output_chunk = @output_chunk + @nl + '					' + @fk_backer + '= value;'
                                IF @renderINotify = 1 
                                    SET @output_chunk = @output_chunk + @nl + '					RaisePropertyChanged("' + @fk_property + '");'
                                SET @output_chunk = @output_chunk + @nl + '				}'

                                SET @output_chunk = @output_chunk + @nl + '			}'
                                SET @output_chunk = @output_chunk + @nl 
                            END

						--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                        BEGIN -- output chunk
                            IF @returnResultSet = 1 AND @renderDomainObjects = 1
                                BEGIN
                                    INSERT  INTO @tbl_output_type ( [name], line )
                                    VALUES  ( NULL, @output_chunk )
                                END
                            ELSE 
								IF @renderDomainObjects = 1
									PRINT @output_chunk
                            SET @output_chunk = ''
                        END
                        --<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                        
                        LBL_NEXT_ONE_TO_MANY:
                        FETCH NEXT FROM cForeignKeys INTO @fk_schema, @fk_name, @pk_schema, @pk_name
                    END

                CLOSE cForeignKeys
                DEALLOCATE cForeignKeys
                SET @output_chunk = @output_chunk + @nl + '			#endregion'
                SET @output_chunk = @output_chunk + @nl
            END

        BEGIN -- equality methods
        

			-- get a pk count. if no pk, no way to compare
            SELECT  @count1 = COUNT(*)
            FROM    @tbl_unique_key_columns ukc
            WHERE   table_schema = @current_object_schema
                    AND table_name = @current_object_name
                    AND is_primary_key = 1
            IF @count1 > 0 
                BEGIN 
					SET @output_chunk = @output_chunk + @nl + '			#region Object'
					SET @output_chunk = @output_chunk + @nl
                
					-- using this as reference http://msdn.microsoft.com/en-us/library/ms173147(VS.80).aspx
 
                    SET @output_chunk = @output_chunk + @nl + '           public override bool Equals(object obj)'
                    SET @output_chunk = @output_chunk + @nl + '           {'
                    SET @output_chunk = @output_chunk + @nl + '               if (ReferenceEquals(null, obj)) return false;'
                    SET @output_chunk = @output_chunk + @nl + '               if (ReferenceEquals(this, obj)) return true;'
                    SET @output_chunk = @output_chunk + @nl + '                ' + @generated_class_name + ' temp = obj as ' + @generated_class_name + ';'
                    SET @output_chunk = @output_chunk + @nl + '               if (temp == null) { return false; }'
					-- if it is a value type                    
                    
                    --SET @output_chunk = @output_chunk + @nl + '                   if (null != temp)'
                    --SET @output_chunk = @output_chunk + @nl + '                   {'
                    SET @output_chunk = @output_chunk + @nl + '               bool equals = '
					-- coalesce keys
                    SELECT  @output_chunk = COALESCE(@output_chunk + '', '') + @nl + '                       (this.' + ic.object_column_name + '.Equals(temp.' + ic.object_column_name + ')) &&'
                    FROM    @tbl_unique_key_columns ukc
                    INNER JOIN @tbl_identifier_cache ic
                    ON      ukc.table_schema = ic.original_object_schema
                            AND ukc.table_name = ic.original_object_name
                            AND ukc.column_name = ic.orginal_column_name
                    WHERE   table_schema = @current_object_schema
                            AND table_name = @current_object_name
                            AND is_primary_key = 1
                    ORDER BY column_id 
								   	


                    SET @output_chunk = @output_chunk + @nl + '                           true;'
                    --SET @output_chunk = @output_chunk + @nl + '               }'
                    SET @output_chunk = @output_chunk + @nl + '               return equals;'
                    SET @output_chunk = @output_chunk + @nl + '           }'
                    SET @output_chunk = @output_chunk + @nl + '           public override int GetHashCode()'
                    SET @output_chunk = @output_chunk + @nl + '           {'
                    SET @output_chunk = @output_chunk + @nl + '               int hash = 121807;'


					--'_' + member_name_camel + '.GetHashCode();'
                    SET @tmp_sql = NULL
                    SELECT  @tmp_sql = COALESCE(@tmp_sql + @nl, '') + '               hash += ' + CASE is_value_type
                                                                                                    WHEN 1 THEN CASE is_nullable
                                                                                                                  WHEN 1 THEN '(null == _' + member_name_camel + '? 0 : _' + member_name_camel + '.GetHashCode());'
                                                                                                                  ELSE '_' + member_name_camel + '.GetHashCode();'
                                                                                                                END
                                                                                                    ELSE '(null == _' + member_name_camel + '? 0 : _' + member_name_camel + '.GetHashCode());'
                                                                                                  END
                    FROM    @tbl_all_table_columns
                    WHERE   [object_id] = @current_object_id
                            AND is_primary_key = 1
					
                    SET @output_chunk = @output_chunk + @nl + ISNULL(@tmp_sql, '')

                    SET @output_chunk = @output_chunk + @nl + '               return hash;'
                    SET @output_chunk = @output_chunk + @nl + '           }'					
					SET @output_chunk = @output_chunk + @nl + '			#endregion'
					SET @output_chunk = @output_chunk + @nl
                    
                END
        END	
		
		BEGIN -- render end of class
			SET @output_chunk = @output_chunk + @nl + '		}'
			SET @output_chunk = @output_chunk + @nl
			IF @renderSchemaNamespace = 1 
				BEGIN
					-- render end of schema namespace
					SET @output_chunk = @output_chunk + @nl + '	}'
				END
			--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
			BEGIN -- output chunk
				IF @returnResultSet = 1 AND @renderDomainObjects = 1
					BEGIN
						INSERT  INTO @tbl_output_type ( [name], line )
						VALUES  ( NULL, @output_chunk )
					END
				ELSE 
					IF @renderDomainObjects = 1
						PRINT @output_chunk
					SET @output_chunk = ''
			END
			--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
		END		

        BEGIN -- output class map only if current object has primary keys. if no keys the mapper throw errors when the map is read.

			
			INSERT  INTO @tbl_tmp_class_map ( [line] )
			VALUES  ( '				PostMap();' )
			INSERT  INTO @tbl_tmp_class_map ( [line] )
			VALUES  ( '			}' )
			INSERT  INTO @tbl_tmp_class_map ( [line] )
			VALUES  ( '		}' )
			INSERT  INTO @tbl_tmp_class_map ( [line] )
			VALUES  ( '	}' )
		        
            SELECT  @primary_key_count = COUNT(*)
            FROM    @tbl_unique_key_columns
            WHERE   table_schema = @current_object_schema
                    AND table_name = @current_object_name

            SELECT  @count1 = COUNT(*)
            FROM    @tbl_tmp_class_map
            
            IF @renderFluentNHibernate = 1 AND @count1 > 0 AND @primary_key_count > 0 
                INSERT  INTO @tbl_output_class_map ( [name], line ) SELECT  [name], line FROM    @tbl_tmp_class_map
        END

        FETCH NEXT FROM cCurrentObject INTO @current_object_id, @current_object_schema, @current_object_name, @current_object_type
    END
 

BEGIN -- render supporting class code
	--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    BEGIN -- output chunk
        IF @returnResultSet = 1 
            BEGIN
                INSERT  INTO @tbl_output_type ( [name], line )
                VALUES  ( 'SupportClasses', NULL )
            END
        ELSE 
            PRINT @output_chunk
        SET @output_chunk = ''
    END
	--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
	    
	-- render base object
    IF @renderCommonBase = 1 
        BEGIN
			-- remove assembly dependancies by packaging the required types with the dto.
		   -- render attributes
            IF @renderDataContract = 1 
                BEGIN
					--
                    SET @output_chunk = @output_chunk + @nl + '			[DataContract('
                    IF LEN(@dataContractNamespace) > 0 
                        SET @output_chunk = @output_chunk + 'Namespace="' + @dataContractNamespace + CASE @renderSchemaNamespace
                                                                                                       WHEN 1 THEN '.' + @generated_class_namespace
                                                                                                       ELSE ''
                                                                                                     END + '",'
                    SET @output_chunk = @output_chunk + 'Name="' + @commonBaseDataContractName + '")]'	--TODO: Ensure this works in WCF. I think not
                END

            IF @renderSerializable = 1 
                SET @output_chunk = @output_chunk + @nl + '			[Serializable]'

            SET @output_chunk = @output_chunk + @nl + '		    public partial class ' + REPLACE(@commonBaseName, '$', 'T') + CASE @renderINotify
                                                                                                                                      WHEN 1 THEN ' : INotifyPropertyChanged'
                                                                                                                                      ELSE ''
                                                                                                                                    END + CASE CHARINDEX('<', @commonBaseName)
                                                                                                                                            WHEN 0 THEN ''
                                                                                                                                            ELSE ' where T:new()'
                                                                                                                                          END

            SET @output_chunk = @output_chunk + @nl + '		    {'

			--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            BEGIN -- output chunk
                IF @returnResultSet = 1
                    AND ISNULL(@output_chunk, '') ! = '' 
                    BEGIN
                        INSERT  INTO @tbl_output_type ( [name], line )
                        VALUES  ( NULL, @output_chunk )
                    END
                ELSE 
                    PRINT @output_chunk
                SET @output_chunk = ''
            END
			--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

            IF @renderINotify = 1 
                BEGIN
					SET @output_chunk = ''
                    SET @output_chunk = @output_chunk + @nl + '		        private const string ERROR_MSG = "{0} is not a public property of {1}";'
                    SET @output_chunk = @output_chunk + @nl + '		        private static readonly Dictionary<string, PropertyChangedEventArgs> eventArgCache;'
                    SET @output_chunk = @output_chunk + @nl + '		        static ' + REPLACE(REPLACE(REPLACE(@commonBaseName, '$', ''), '<', ''), '>', '') + '()'
                    SET @output_chunk = @output_chunk + @nl + '		        {'
                    SET @output_chunk = @output_chunk + @nl + '		            eventArgCache = new Dictionary<string, PropertyChangedEventArgs>();'
                    SET @output_chunk = @output_chunk + @nl + '		        }'
                    SET @output_chunk = @output_chunk + @nl + '		 '
                    SET @output_chunk = @output_chunk + @nl + '		        protected ' + REPLACE(REPLACE(REPLACE(@commonBaseName, '$', ''), '<', ''), '>', '') + '(){} '
                    SET @output_chunk = @output_chunk + @nl + '		        [field: NonSerialized] '
                    SET @output_chunk = @output_chunk + @nl + '		        public event PropertyChangedEventHandler PropertyChanged;'
                    SET @output_chunk = @output_chunk + @nl + '		        public static PropertyChangedEventArgs GetPropertyChangedEventArgs(string propertyName)'
                    SET @output_chunk = @output_chunk + @nl + '		        {'
                    SET @output_chunk = @output_chunk + @nl + '		            if (String.IsNullOrEmpty(propertyName))'
                    SET @output_chunk = @output_chunk + @nl + '		                throw new ArgumentException("propertyName cannot be null or empty.");'
                    SET @output_chunk = @output_chunk + @nl + '		            PropertyChangedEventArgs args; '
                    SET @output_chunk = @output_chunk + @nl + '		            lock (typeof (' + REPLACE(@commonBaseName, '$', 'T') + ')) '
                    SET @output_chunk = @output_chunk + @nl + '		            {'
                    SET @output_chunk = @output_chunk + @nl + '		                bool isCached = eventArgCache.ContainsKey(propertyName); '
                    SET @output_chunk = @output_chunk + @nl + '		                if (!isCached) '
                    SET @output_chunk = @output_chunk + @nl + '		                {'
                    SET @output_chunk = @output_chunk + @nl + '		                    eventArgCache.Add(propertyName,new PropertyChangedEventArgs(propertyName));'
                    SET @output_chunk = @output_chunk + @nl + '		                }'
                    SET @output_chunk = @output_chunk + @nl + '		                args = eventArgCache[propertyName];'
                    SET @output_chunk = @output_chunk + @nl + '		            }'
                    SET @output_chunk = @output_chunk + @nl + '		            return args; '
                    SET @output_chunk = @output_chunk + @nl + '		        }'
                    SET @output_chunk = @output_chunk + @nl + '		        protected virtual void AfterPropertyChanged(string propertyName){} '
                    SET @output_chunk = @output_chunk + @nl + '		        protected void RaisePropertyChanged(string propertyName) '
                    SET @output_chunk = @output_chunk + @nl + '		        {'
                    SET @output_chunk = @output_chunk + @nl + '		            VerifyProperty(propertyName);'
                    SET @output_chunk = @output_chunk + @nl + '		            PropertyChangedEventHandler handler = PropertyChanged; '
                    SET @output_chunk = @output_chunk + @nl + '		            if (handler != null) '
                    SET @output_chunk = @output_chunk + @nl + '		            {'
                    SET @output_chunk = @output_chunk + @nl + '		                PropertyChangedEventArgs args = GetPropertyChangedEventArgs(propertyName); '
                    SET @output_chunk = @output_chunk + @nl + '		                handler(this, args); '
                    SET @output_chunk = @output_chunk + @nl + '		            }'
                    SET @output_chunk = @output_chunk + @nl + '		            AfterPropertyChanged(propertyName);'
                    SET @output_chunk = @output_chunk + @nl + '		        }'
                    SET @output_chunk = @output_chunk + @nl + '		        [Conditional("DEBUG")] '
                    SET @output_chunk = @output_chunk + @nl + '		        private void VerifyProperty(string propertyName) '
                    SET @output_chunk = @output_chunk + @nl + '		        {'
                    SET @output_chunk = @output_chunk + @nl + '		            Type type = GetType(); '
                    SET @output_chunk = @output_chunk + @nl + '		            PropertyInfo propInfo = type.GetProperty(propertyName);'
                    SET @output_chunk = @output_chunk + @nl + '		            if (propInfo == null)'
                    SET @output_chunk = @output_chunk + @nl + '		            {'
                    SET @output_chunk = @output_chunk + @nl + '		                string msg = string.Format(ERROR_MSG,propertyName,type.FullName);'
                    SET @output_chunk = @output_chunk + @nl + '		                Debug.Fail(msg); '
                    SET @output_chunk = @output_chunk + @nl + '		            }'
                    SET @output_chunk = @output_chunk + @nl + '		        }'

					--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                    BEGIN -- output chunk
                        IF @returnResultSet = 1
                            AND ISNULL(@output_chunk, '') ! = '' 
                            BEGIN
                                INSERT  INTO @tbl_output_type ( [name], line )
                                VALUES  ( NULL, @output_chunk )
                            END
                        ELSE 
                            PRINT @output_chunk
                        SET @output_chunk = ''
                    END
					--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                END

            SET @output_chunk = @output_chunk + @nl + '				protected static bool ByteArrayCompare(byte[] a1, byte[] a2) '
            SET @output_chunk = @output_chunk + @nl + '				{'
            SET @output_chunk = @output_chunk + @nl + '					if(a1 == null || a2 == null || a1.Length!=a2.Length)'
            SET @output_chunk = @output_chunk + @nl + '					 return false;'
            SET @output_chunk = @output_chunk + @nl + '					 for(int i=0; i<a1.Length; i++)'
            SET @output_chunk = @output_chunk + @nl + '						if(a1[i]!=a2[i])'
            SET @output_chunk = @output_chunk + @nl + '							return false;'
            SET @output_chunk = @output_chunk + @nl + '					return true;'
            SET @output_chunk = @output_chunk + @nl + '				}'
            SET @output_chunk = @output_chunk + @nl + '		    }'
            
			--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
			BEGIN -- output chunk
				IF @returnResultSet = 1
					AND ISNULL(@output_chunk, '') ! = '' 
					BEGIN
						INSERT  INTO @tbl_output_type ( [name], line )
						VALUES  ( NULL, @output_chunk )
					END
				ELSE 
					PRINT @output_chunk
				SET @output_chunk = ''
			END
			--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<	            
        END

    IF @renderFluentNHibernate = 1 -- render ClassMap base
        BEGIN
		    SET @output_chunk = @output_chunk + @nl +'	namespace FluentClassMaps                                                                                                           '
			SET @output_chunk = @output_chunk + @nl +'    {                                                                                                                               '
			SET @output_chunk = @output_chunk + @nl +'        using System.Linq;                                                                                                          '
			SET @output_chunk = @output_chunk + @nl +'        using FluentNHibernate.Mapping;                                                                                             '
			SET @output_chunk = @output_chunk + @nl +'        /// <summary>                                                                                                               '
			SET @output_chunk = @output_chunk + @nl +'        /// A simple wrapper to facilitate tweaking of maps                                                                         '
			SET @output_chunk = @output_chunk + @nl +'        /// generated by DeadSimpleDTO script                                                                                       '
			SET @output_chunk = @output_chunk + @nl +'        ///                                                                                                                         '
			SET @output_chunk = @output_chunk + @nl +'        /// There is probably a more elegant way to do this using Fluently                                                          '
			SET @output_chunk = @output_chunk + @nl +'        /// but this feels only a little dirty to me.                                                                               '
			SET @output_chunk = @output_chunk + @nl +'        ///                                                                                                                         '
			SET @output_chunk = @output_chunk + @nl +'        /// I intend to consolidate as many of these methods as possible.                                                           '
			SET @output_chunk = @output_chunk + @nl +'        ///                                                                                                                         '
			SET @output_chunk = @output_chunk + @nl +'        /// TODO: guards with good exception messages for configuration errors                                                      '
			SET @output_chunk = @output_chunk + @nl +'        /// </summary>                                                                                                              '
			SET @output_chunk = @output_chunk + @nl +'        /// <typeparam name="T"></typeparam>                                                                                        '
			SET @output_chunk = @output_chunk + @nl +'        public partial class ClassMapWrap<T> : ClassMap<T>                                                                          '
			SET @output_chunk = @output_chunk + @nl +'        {                                                                                                                           '
			SET @output_chunk = @output_chunk + @nl +'            /// <summary>                                                                                                           '
			SET @output_chunk = @output_chunk + @nl +'            /// Called before the mapping begins                                                                                    '
			SET @output_chunk = @output_chunk + @nl +'            /// </summary>                                                                                                          '
			SET @output_chunk = @output_chunk + @nl +'            public virtual void PreMap() { }                                                                                        '
			SET @output_chunk = @output_chunk + @nl +'                                                                                                                                    '
			SET @output_chunk = @output_chunk + @nl +'            /// <summary>                                                                                                           '
			SET @output_chunk = @output_chunk + @nl +'            /// Called after mapping has completed                                                                                  '
			SET @output_chunk = @output_chunk + @nl +'            /// </summary>                                                                                                          '
			SET @output_chunk = @output_chunk + @nl +'            public virtual void PostMap() { }                                                                                       '
			SET @output_chunk = @output_chunk + @nl +'                                                                                                                                    '
			SET @output_chunk = @output_chunk + @nl +'                                                                                                                                    '
			SET @output_chunk = @output_chunk + @nl +'            /// <summary>                                                                                                           '
			SET @output_chunk = @output_chunk + @nl +'            /// </summary>                                                                                                          '
			SET @output_chunk = @output_chunk + @nl +'            /// <typeparam name="M">The member type</typeparam>                                                                     '
			SET @output_chunk = @output_chunk + @nl +'            /// <param name="memberName">The member name</param>                                                                    '
			SET @output_chunk = @output_chunk + @nl +'            /// <returns></returns>                                                                                                 '
			SET @output_chunk = @output_chunk + @nl +'            public virtual ManyToManyPart<M> ManyToMany<M>(string memberName)                                                       '
			SET @output_chunk = @output_chunk + @nl +'            {                                                                                                                       '
			SET @output_chunk = @output_chunk + @nl +'                return (ManyToManyPart<M>)collections.First(c => c.GetCollectionMapping().Member.MemberInfo.Name == memberName);           '
			SET @output_chunk = @output_chunk + @nl +'            }                                                                                                                       '
			SET @output_chunk = @output_chunk + @nl +'            /// <summary>                                                                                                           '
			SET @output_chunk = @output_chunk + @nl +'            /// </summary>                                                                                                          '
			SET @output_chunk = @output_chunk + @nl +'            /// <typeparam name="M">The member type</typeparam>                                                                     '
			SET @output_chunk = @output_chunk + @nl +'            /// <param name="memberName">The member name</param>                                                                    '
			SET @output_chunk = @output_chunk + @nl +'            /// <returns></returns>                                                                                                 '
			SET @output_chunk = @output_chunk + @nl +'            public virtual OneToManyPart<M> OneToMany<M>(string memberName)                                                         '
			SET @output_chunk = @output_chunk + @nl +'            {                                                                                                                       '
			SET @output_chunk = @output_chunk + @nl +'                return (OneToManyPart<M>)collections.First(c => c.GetCollectionMapping().Member.MemberInfo.Name == memberName);            '
			SET @output_chunk = @output_chunk + @nl +'            }                                                                                                                       '
			SET @output_chunk = @output_chunk + @nl +'                                                                                                                                    '
			SET @output_chunk = @output_chunk + @nl +'            /// <summary>                                                                                                           '
			SET @output_chunk = @output_chunk + @nl +'            ///                                                                                                                     '
			SET @output_chunk = @output_chunk + @nl +'            /// </summary>                                                                                                          '
			SET @output_chunk = @output_chunk + @nl +'            /// <param name="memberName">The member name</param>                                                                    '
			SET @output_chunk = @output_chunk + @nl +'            /// <returns></returns>                                                                                                 '
			SET @output_chunk = @output_chunk + @nl +'            public virtual PropertyPart Property(string memberName)                                                                 '
			SET @output_chunk = @output_chunk + @nl +'            {                                                                                                                       '
			SET @output_chunk = @output_chunk + @nl +'                return (PropertyPart)Properties.FirstOrDefault(x => x.GetPropertyMapping().Member.MemberInfo.Name == memberName);        '
			SET @output_chunk = @output_chunk + @nl +'                                                                                                                                    '
			SET @output_chunk = @output_chunk + @nl +'            }                                                                                                                       '
			SET @output_chunk = @output_chunk + @nl +'            /// <summary>                                                                                                           '
			SET @output_chunk = @output_chunk + @nl +'            ///                                                                                                                     '
			SET @output_chunk = @output_chunk + @nl +'            /// </summary>                                                                                                          '
			SET @output_chunk = @output_chunk + @nl +'            /// <typeparam name="M">The member type</typeparam>                                                                     '
			SET @output_chunk = @output_chunk + @nl +'            /// <param name="memberName">The member name</param>                                                                    '
			SET @output_chunk = @output_chunk + @nl +'            /// <returns></returns>                                                                                                 '
			SET @output_chunk = @output_chunk + @nl +'            public virtual ManyToOnePart<M> ManyToOne<M>(string memberName)                                                         '
			SET @output_chunk = @output_chunk + @nl +'            {                                                                                                                       '
			SET @output_chunk = @output_chunk + @nl +'                return (ManyToOnePart<M>)references.FirstOrDefault(x => x.GetManyToOneMapping().Member.MemberInfo.Name == memberName);   '
			SET @output_chunk = @output_chunk + @nl +'                                                                                                                                    '
			SET @output_chunk = @output_chunk + @nl +'            }                                                                                                                       '
			SET @output_chunk = @output_chunk + @nl +'            /// <summary>                                                                                                           '
			SET @output_chunk = @output_chunk + @nl +'            ///                                                                                                                     '
			SET @output_chunk = @output_chunk + @nl +'            /// </summary>                                                                                                          '
			SET @output_chunk = @output_chunk + @nl +'            /// <typeparam name="M">The member type</typeparam>                                                                     '
			SET @output_chunk = @output_chunk + @nl +'            /// <param name="memberName">The member name</param>                                                                    '
			SET @output_chunk = @output_chunk + @nl +'            /// <returns></returns>                                                                                                 '
			SET @output_chunk = @output_chunk + @nl +'            public virtual OneToOnePart<M> OneToOne<M>(string memberName)                                                           '
			SET @output_chunk = @output_chunk + @nl +'            {                                                                                                                       '
			SET @output_chunk = @output_chunk + @nl +'                return (OneToOnePart<M>)oneToOnes.FirstOrDefault(x => x.GetOneToOneMapping().Name == memberName);                   '
			SET @output_chunk = @output_chunk + @nl +'                                                                                                                                    '
			SET @output_chunk = @output_chunk + @nl +'            }                                                                                                                       '
			SET @output_chunk = @output_chunk + @nl +'                                                                                                                                    '
			SET @output_chunk = @output_chunk + @nl +'        }                                                                                                                           '
			SET @output_chunk = @output_chunk + @nl +'    }'


			--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
			BEGIN -- output chunk
				IF @returnResultSet = 1
					AND ISNULL(@output_chunk, '') ! = '' 
					BEGIN
						INSERT  INTO @tbl_output_type ( [name], line )
						VALUES  ( NULL, @output_chunk )
					END
				ELSE 
					PRINT @output_chunk
				SET @output_chunk = ''
			END
			--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<	

            IF @returnResultSet = 1 
                BEGIN
                    INSERT  INTO @tbl_output_type( [name], line )
                            SELECT  [name], line
                            FROM    @tbl_output_class_map 
                END
            ELSE 
                BEGIN
					-- TODO: open cursor and output line by line, will be too big for coalesce
					DECLARE cClassMapOut CURSOR READ_ONLY FOR 
					SELECT line FROM @tbl_output_class_map
					OPEN cClassMapOut
					FETCH NEXT FROM cClassMapOut INTO @tmp_sql
					WHILE @@FETCH_STATUS =0
						BEGIN
							PRINT @tmp_sql
							FETCH NEXT FROM cClassMapOut INTO @tmp_sql
						END
					
					CLOSE cClassMapOut
					DEALLOCATE cClassMapOut
                END
        
        END    



END

BEGIN -- render end of root namespace

    --SET @output_chunk = @nl + '// Parameters'
    --SET @output_chunk = @output_chunk + @nl + '// -----------------------------------------------------------------------'

    --SET @output_chunk = @output_chunk + @nl + '// rootNamespace           = ' + ISNULL(@rootNamespace, '<empty>')
    --SET @output_chunk = @output_chunk + @nl + '// commonBaseName          = ' + ISNULL(@commonBaseName, '<empty>')
    --SET @output_chunk = @output_chunk + @nl + '// memberNameSubstitutions = ' + ISNULL(@memberNameSubstitutions, '<empty>')
    --SET @output_chunk = @output_chunk + @nl + '// namespacesToInclude     = ' + ISNULL(@namespacesToInclude, '<empty>')
    --SET @output_chunk = @output_chunk + @nl + '// dataContractNamespace   = ' + ISNULL(@dataContractNamespace, '<empty>')
    --SET @output_chunk = @output_chunk + @nl + '// objectsToRender         = ' + ISNULL(@objectsToRender, '<empty>')
    --SET @output_chunk = @output_chunk + @nl + '// objectsToExclude        = ' + ISNULL(@objectsToExclude, '<empty>')
    --SET @output_chunk = @output_chunk + @nl + '// membersToExclude        = ' + ISNULL(@membersToExclude, '<empty>')
    --SET @output_chunk = @output_chunk + @nl + '// identifierMode          = ' + ISNULL(@identifierMode, '<empty>')
    --SET @output_chunk = @output_chunk + @nl + '// listInstanceType        = ' + ISNULL(@listInstanceType, '<empty>')
    --SET @output_chunk = @output_chunk + @nl + '// listMemberType          = ' + ISNULL(@listMemberType, '<empty>')

    --SET @output_chunk = @output_chunk + @nl + '// renderSchemaNamespace      = ' + CAST(@renderSchemaNamespace AS VARCHAR(50))
    --SET @output_chunk = @output_chunk + @nl + '// renderDataContract         = ' + CAST(@renderDataContract AS VARCHAR(50))
    --SET @output_chunk = @output_chunk + @nl + '// renderSerializable         = ' + CAST(@renderSerializable AS VARCHAR(50))
    --SET @output_chunk = @output_chunk + @nl + '// renderLoaderAttributes     = ' + CAST(@renderLoaderAttributes AS VARCHAR(50))
    --SET @output_chunk = @output_chunk + @nl + '// renderINotify              = ' + CAST(@renderINotify AS VARCHAR(50))
    --SET @output_chunk = @output_chunk + @nl + '// renderRelationships        = ' + CAST(@renderOneToManyRelations AS VARCHAR(50))
    --SET @output_chunk = @output_chunk + @nl + '// renderCommonBase           = ' + CAST(@renderCommonBase AS VARCHAR(50))
    --SET @output_chunk = @output_chunk + @nl + '// renderManyToOneRelations   = ' + CAST(@renderManyToOneRelations AS VARCHAR(50))
    --SET @output_chunk = @output_chunk + @nl + '// renderManyToManyRelations  = ' + CAST(@renderManyToManyRelations AS VARCHAR(50))
    
    
    --SET @output_chunk = @output_chunk + @nl + '// returnResultSet            = ' + CAST(@returnResultSet AS VARCHAR(50))
    --SET @output_chunk = @output_chunk + @nl + '// renderFluentNHibernate     = ' + CAST(@renderFluentNHibernate AS VARCHAR(50))

	
    --SET @output_chunk = @output_chunk + @nl
    --SET @output_chunk = @output_chunk + @nl	

	-- render end of root namespace
    SET @output_chunk = @output_chunk + @nl + '}'


	--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    BEGIN -- output chunk
        IF @returnResultSet = 1 
            BEGIN
                INSERT  INTO @tbl_output_type ( [name], line )
                VALUES  ( 'NAMESPACE_CLOSE', @output_chunk )
            END
        ELSE 
            PRINT @output_chunk
        SET @output_chunk = ''
    END
	--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<    
END

CLOSE cCurrentObject
DEALLOCATE cCurrentObject

-- select it for our ADO customers
-- USAGE: the output is chunked out with each type being preceeded by a name / null line
--	including the support classes, if rendered. WITH the exception of the NAMESPACE_OPEN and
--	NAMESPACE_CLOSE records. These are just what they sound like. This allows splitting the
--	output into separate files, if desired.
IF @returnResultSet = 1 
    SELECT  [name], line
    FROM    @tbl_output_type

--TODO: add rename of tables/views
-- fixed bug in list type